VBA: Cut, Copy, Paste from a Macro

August 18th, 2004 | Categories: VBA | Tags: , , , ,

It’s pretty easy to cut and paste from a macro. Here are a few examples. The code works identical for copy, just replace the word cut with copy!

This one cuts and pastes a single cell, a1 over to b1:

Sub OneCell()

    Range("A1").Select
    Selection.Cut
    Range("B1").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

This one cuts and pastes an entire column, A over to B:

Sub OneColumn()

    Range("A:A").Select
    Selection.Cut
    Range("B:B").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

This one cuts and pastes an entire row, 1 over to 2:

Sub OneRow()

    Range("1:1").Select
    Selection.Cut
    Range("2:2").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. matthew
    October 17th, 2008 at 08:46
    Reply | Quote | #1

    How do you paste it into specific cells in another worksheet?

  2. Diego Castro
    March 30th, 2009 at 16:25
    Reply | Quote | #2

    From VBA help:
    Worksheets(“Sheet1″).Range(“A1:D4″).Copy _
    Destination:=Worksheets(“Sheet2″).Range(“E5″)

    Now I understand :)

    • Rashed
      February 3rd, 2010 at 03:50
      Reply | Quote | #3

      Simply Excellent Code

    • Ram
      May 2nd, 2011 at 13:21
      Reply | Quote | #4

      Hi, I want to paste only values of particular range to sheet2 if sheet 1 meets criteria that is given date is matched

  3. Marcelo
    June 1st, 2009 at 11:19
    Reply | Quote | #5

    Hi,

    Could you please help-me in this situation…
    How to make this function work?
    I just need to call it in a cell like “=test_copy(B1)” to see it copied into B2
    I am new to vba, but I’m able to do some perl,c++

    regards,
    Marcelo

    Public Function test_copy(Var01 As Range)
    Var01.Select
    Selection.Copy
    Range(“B2″).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End Function

  4. June 10th, 2009 at 21:52
    Reply | Quote | #6

    Is there a way to cut a row/rows and insert it/them into a sorted order on a different page with other information? I can’t just insert them at the bottom and sort because the rows on the other page are merged and cannot be sorted. This is the last piece to my VBA puzzle. Thank you!

  5. Zach
    July 15th, 2009 at 19:45
    Reply | Quote | #7

    how do you copy a column from one workbook and paste it in another?

  6. Chris
    August 7th, 2009 at 13:58
    Reply | Quote | #8

    How do you copy Range A7:C11 from the first sheet to all sheets of the workbook (also to A7:C11 on every sheet)? Thanks!

  7. Jeff
    August 25th, 2009 at 00:16
    Reply | Quote | #9

    Hi, when complex vba routines run in the background of excel, it becomes impossible to use copy and pate in other applications. Any workaround for this? Cheers, Jeff

  8. Sandheep
    August 27th, 2009 at 14:56

    How do I copy the cell no. and paste it another cell. Suppose I want the cell no. of the word “Significant” in the worksheet. Assuming it is in B4. How do I paste it in C1 using macro? The macro should be able to find the cell no.

  9. Rahul
    November 23rd, 2009 at 15:47

    Hi

    I have data in 50 worksheets in a workbook (in same format). Can I copy all data and paste it in a New Worksheet, so that a Master File can be created.

  10. Aaron
    January 8th, 2010 at 20:29

    I have four rows of data that need to be pastes into another worksheet in 1 column? How do I do this?

    I have been trying to set cells equal by value, but for some reason, they can’t transpose automatically.

    I have also tried the if then statements so that if the data is greater than zero, then the cell automatically paste into the other sheet, but that wont work either? Any ideas as to how to get multiple rows paste into one column across sheets?

  11. Logs
    January 19th, 2010 at 23:25

    Hi,
    I need to do a find of a certain word in my worksheet, then cur the entire row it is in and insert that into the first row. Which is easy enough.. but the row the particular word is in changes each time… How can i make it cut a different row each time the Macro is run?

  12. Norman
    March 8th, 2010 at 17:14

    Is there a way, using an input form, to tell the macro where to start pasting information? For example:

    The typical command is: Range(“B2″).Select
    I want to be able to tell the macro a different cell to copy to starting somewhere other than “B2″. Maybe B3 or B4.

  13. Mike
    March 23rd, 2010 at 11:20

    Hi, Im new to all of this excel formatting stuff, but im trying to accomplish a task and wondering if someone could guide me as to what tools i need to use. Suppose I have one spreadsheet with one column of items 1,2,3…n. Then I have another sheet with two columns…. one with 1a, 1b, 1c….2a…2b…2c. What I want to do is paste the value from the first sheet, 1, onto 1a….then the value 2 into 2a…whilst ignoring the subsequent 1b 1c inbetween. Does anyone know the easiest way to approach this? thanks in advance, Mike

  14. Mike
    March 23rd, 2010 at 11:23

    Sorry to follow up my own post, but below is the effect I was trying to describe above. So im trying to paste the value ’1′ on the right hand side into 1 on the left, then ignore 1b, 1c etc (these would actually be just blank in my real problem worksheet), and then paste 2 from the right into 2 on the left.

    1 1
    1b 2
    1c 3
    1d 4
    2 5
    2b
    2c
    2d
    3
    3b
    3c

  15. May 11th, 2010 at 19:59

    very goodThat’s some good solid information, thanks for posting it!

    • May 19th, 2010 at 04:48

      Hi..
      Am very new to VB. I am working on a spreadsheet that cut and paste auto when a certain keyword is keyed in. I tried to copy and paste the first program but it is not working.Please help me out here. Thanks.

    • VC
      October 5th, 2010 at 19:49

      Hi,
      I have a file with 150-200 different rows. I need to copy and paste each row into its own separate worksheet or worbook. Basically, I will need to create 150-200 different worksheets with one line each all based on a master file.

      Does anyone know how to accomplish this?

  16. Jeff
    August 27th, 2010 at 17:02

    Why in the world you would do this this way? Selecting in VBA code is pointless. The same operation can be performed with this simple one line of code. And this can be modified as needed to meet the other macros needs.

    Sub OneCell()
    Range(“A1″).Cut Destination:=Range(“B1″)
    End Sub

  17. Appu
    October 1st, 2010 at 09:19

    +how to cut the row and copy the row in another worksheet in excel using macro

  18. womi
    October 18th, 2010 at 18:12

    Can someone please help me? I am not at all familiar with VBA so would like a code as detailed as possible (with comments).

    Here’s what I am trying to do – I would like to automate copying of data from one workbook to another based on some criteria. For example – I am making up the layout of my data for confidentiality reasons but this should give a pretty good idea of what I want -

    I have a workbook called Apartments with a sheet called “Info”. The columns (not starting at the first row) are labeled as “region”, “Number of rooms”, “price”, “square footage”, “security deposit”.

    Then I have another workbook called Detail_info with sheets called “south_1″, “south_2″, “south_3″, “north_1″, “north_2″, etc.

    What I need to do is somehow automate filtering of workbook Apartments (sheet-Info) to copy Region (ex. South) and Number of Rooms (ex. 1) and then paste this information to workbook Detail_info – sheet South_1.
    I need to do this for all the combinations of region/number of rooms.My list isn’t long so I can type out the combinations in the code if I have a detailed template to work with. Please someone help me!

  19. November 23rd, 2010 at 19:38

    Could anybody help a brotha out?

    I have excel workbook called that has a tab called “master” that is populated with data from seperate excel workbooks (up 50 different ones!). I only need to copy some of the data from source into the “master” sheet. Here’s what I’m trying to accomplish

    First Data Set
    copy all data from column A3 until the words “Grand Totals:” (this varies from 1 row all the way to 500 rows) from source to first empty cell under column “c” in “master”

    Second Data Set
    copy data from U1 in source to first empty cell under column “B” in “master”

    Third Data Set
    copy data from G1 in source to first empty cell under column “A” in “master”

    Any ideas?

  20. Ashley
    December 27th, 2010 at 18:45

    I am using the following code, but I need it to copy Column A & B onto the new worksheet, but to keep the data on the same row in Sheet2 as it was on Sheet 1. Can someone tell me what I need to add?

    I have Sheet2 copied over exactly form Sheet1 so that I can print it and it look the same. This is our invoice. Our customers order things and if it is Back Ordered I want it to copy to Sheet 2 so I can make a Back Ordered Order for them.

    Sub Copybo()

    ‘Copy cells of cols A,B from rows containing “BO” in
    ‘col D of the active worksheet (source sheet) to cols
    ‘A,B of Sheet2 (destination sheet)

    ‘Destination Sheet
    Dim DestSheet As Worksheet
    Set DestSheet = Worksheets(“Back Order”)

    Dim sRow As Long ‘row index on source worksheet
    Dim dRow As Long ‘row index on destination worksheet
    Dim sCount As Long
    sCount = 0
    dRow = 1

    For sRow = 1 To Range(“D65536″).End(xlUp).Row
    ‘use pattern matching to find “BO” anywhere in cell
    If Cells(sRow, “I”) Like “*BO*” Then
    sCount = sCount + 1
    dRow = dRow + 1
    ‘copy cols A & B
    DestSheet.Cells(dRow, “A”) = Cells(sRow, “A”)
    DestSheet.Cells(dRow, “B”) = Cells(sRow, “B”)
    End If
    Next sRow

    MsgBox sCount & ” BO rows copied”, vbInformation, “Transfer Done”

    End Sub

  21. Greg
    January 18th, 2011 at 15:47

    Is there any way to copy just the values (not the formulae) with the .Copy syntax? I don’t see any options on the type of copy.

  22. wani
    January 22nd, 2011 at 11:14

    hi there! need your expertise..
    i have columnD of sheet1 to sum lists of data in colB.
    e.g: colA colB colD
    CB 1 =sumif(B:B,”CB”,B:B)
    CB 6
    so, i’ll be getting ’7′ in my D1 cell.
    i ran below code to cut A1:B20 then paste it to last empty row of sheet2. i manage to paste it correctly in sheet2 but somehow my formula in D1(sheet1) changes to >> =sumif(sheet2!B:B,”CB”,sheet2!B:B). how is this so? n how do i correct it?

    code:
    Sub RoundedRectangle2_Click()
    Dim lPart As Long
    Dim ws As Worksheet
    Dim mySheet As Worksheet
    Set mySheet = Worksheets(“checkout”)
    mySheet.Range(“A2:H500″).Cut _
    Destination:=Worksheets(“dataout”).Range(“A65536″).End(xlUp).Offset(1, 0).Range(“A1″)
    Worksheets(“checkout”).Activate

    End Sub

    thanks in advance!

  23. wani
    January 23rd, 2011 at 01:38

    hi everyone. sorry to reply back on my own qtn.
    just wanna share the solution i have found.

    just change “Cut” to “Copy”
    then, clearContents on the copied cells

    mySheet.Range(“A2:H500″).Copy _
    Destination:=Worksheets(“dataout”).Range(“A65536″).End(xlUp).Offset(1, 0).Range(“A1″)
    Range(“A2:H500″).Select
    Selection.ClearContents
    Worksheets(“checkout”).Activate

    \(^_^)/

  24. Sandra
    August 18th, 2011 at 11:36

    Hi!
    This is a complicate case. May be you can help me.
    I have 2 sheets (sheet1 and sheet2) in one xls with a table in each one, same titles, some data is defferent.
    I want:
    1.- compare both tables by column 2
    2.- if data in column 2 of both tables match then, select, copy and insert the entire row in sheet2, so I will have 2 rows with same data in sheet2.

    Can you help me, please.
    Thank you.

  25. Dinesh
    September 9th, 2011 at 22:32

    How to copy a row and column from one sheet and pate it to other sheet n times

  26. Daniel
    September 23rd, 2011 at 07:04

    Does anybody know how to keep active that ‘cancel action’ button on the panel after running a macros?

  27. ssakthish
    January 16th, 2013 at 10:15

    Hi All,
    I have a header say EMP ID, EMP Name, FAX, TELE.
    Now I want a Macro wherein, in the input file I Provide only EMP ID, EMP Name & TELE.
    I want the output as follows.
    1. Excel Should read column wise based on header & If the length exceeds 25 characters then the entire row should be transfered to a new sheet.

  28. Fred DJ
    October 25th, 2013 at 13:10

    Hi all,

    Problem: I want to send an automated email using a macro and this to send a certain range of cells. To keep a clean format of the data (+ layout) I need to paste the data from the excel as a ‘picture’.

    Somebody knows how to do this? This would be very helpful.

  29. December 19th, 2013 at 17:18

    Sometimes the best way to get a picture is to use a program like Snagit or the Windows built-in “Snipping Tool” program. There is also a dynamic “Camera” feature in Excel you could use, but that’s not really for “static” pictures.

    Another way is to use something like the “Export row to an email” feature in our free AI List excel template file (Action Item List). You can download a free copy from http://www.ExcelAutomationHelp.com, or from Download.CNet.com by searching for “AI List” (including the quotes). Right click an AI List cell and try it, to see what I mean.