3 Ways to Copy and Paste Cells with VBA Macros + Video

Bottom line: Learn 3 different ways to copy and paste cells or ranges in Excel with VBA Macros.  This is a 3-part video series and you can also download the file that contains the code.

Skill level: Beginner

3 Ways to Copy and Paste in Excel with VBA Macros

Copy & Paste: The Most Common Excel Action

Copy and paste is probably one of the most common actions you take in Excel.  It's also one of the most common tasks we automate when writing macros.

There are a few different ways to accomplish this task, and the macro recorder doesn't always give you the most efficient VBA code.

In the following three videos I explain:

  • The most efficient method for a simple copy and paste in VBA.
  • The easiest way to paste values.
  • How to use the PasteSpecial method for other paste types.

You can download the file I use in these videos below.  The code is also available at the bottom of the page.

Video #1: The Simple Copy Paste Method

You can watch the playlist that includes all 3 videos at the top of this page.

Video #2: An Easy Way to Paste Values

Video #3: The PasteSpecial Method Explained

VBA Code for the Copy & Paste Methods

Download the workbook that contains the code.

'3 Methods to Copy & Paste with VBA
'Source: https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/
'Author: Jon Acampora

Sub Range_Copy_Examples()
'Use the Range.Copy method for a simple copy/paste

    'The Range.Copy Method - Copy & Paste with 1 line
    Range("A1").Copy Range("C1")
    Range("A1:A3").Copy Range("D1:D3")
    Range("A1:A3").Copy Range("D1")
    
    'Range.Copy to other worksheets
    Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A1")
    
    'Range.Copy to other workbooks
    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy _
        Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")

End Sub


Sub Paste_Values_Examples()
'Set the cells' values equal to another to paste values

    'Set a cell's value equal to another cell's value
    Range("C1").Value = Range("A1").Value
    Range("D1:D3").Value = Range("A1:A3").Value
     
    'Set values between worksheets
    Worksheets("Sheet2").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value
     
    'Set values between workbooks
    Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").Value = _
        Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value
        
End Sub


Sub PasteSpecial_Examples()
'Use the Range.PasteSpecial method for other paste types

    'Copy and PasteSpecial a Range
    Range("A1").Copy
    Range("A3").PasteSpecial Paste:=xlPasteFormats
    
    'Copy and PasteSpecial a between worksheets
    Worksheets("Sheet1").Range("A2").Copy 
    Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteFormulas
    
    'Copy and PasteSpecial between workbooks
    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy
    Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteFormats
    
    'Disable marching ants around copied range
    Application.CutCopyMode = False

End Sub

Paste Data Below the Last Used Row

One of the most common questions I get about copying and pasting with VBA is, how do I paste to the bottom of a range that is constantly changing?  I first want to find the last row of data, then copy & paste below it.

To answer this question, I created a free training video on how to paste data below the last used row in a sheet with VBA.  Can I send you the video?  Please click the image below to get the video.

Paste Data Below Last Used Row VBA Free Training

Free Training on Macros & VBA

The 3 videos above are from my VBA Pro Course.  If you want to learn more about macros and VBA then checkout my free 3-part video training series.

I will also send you info on the VBA Pro Course, that will take you from beginner to expert.  Click the link below to get instant access.

Free Training on Macros & VBA

Please leave a comment below with any questions.  Thanks!

202 comments

Your email address will not be published. Required fields are marked *

  • Hi, I have two workbooks opened (both can be different names) and I want to copy a Range from Workbook1 SheetX (unkown/active) to Workbook2 (where I know the Sheet name)
    I even want the command to open the worksheet from a directory too. Do you have any suggestions on how to do this? Hope to hear from you. Many thanks.

  • Windows(“incident.xlsx”).Activate
    Range(“A:U”).Select ‘this trows the error: application-defined or object-deferred error

  • Hi,

    I need your support, I’m creating a workbook for daily report, and I’m stuck in the summary worksheet, in the VBA I don’t know how to link the information of the new report to be copied in the summary.

    Sub GENERAR_REPORTE()

    Sheets(“REPORTE DIARIO”).Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Shapes.Range(Array(“Button 1”)).Delete
    ActiveSheet.Name = Range(“P7”).Value
    Application.DisplayAlerts = False
    Sheets(“REPORTE DIARIO”).Delete
    Application.DisplayAlerts = True

    End Sub

    Sub REGISTRAR_REPORTE()

    Worksheets(“SUMMARY”).Range(“A6”).Value = ActiveSheet.Name = Range(“P7”).Value.Range(“P7”).Value
    Worksheets(“SUMMARY”).Range(“G6”).Value = ActiveSheet.Name = Range(“P7”).Value.Range(“B15”).Value
    Worksheets(“SUMMARY”).Range(“H6”).Value = ActiveSheet.Name = Range(“P7”).Value.Range(“D15”).Value
    Worksheets(“SUMMARY”).Range(“I6”).Value = ActiveSheet.Name = Range(“P7”).Value.Range(“F15”).Value
    Worksheets(“SUMMARY”).Range(“J6”).Value = Worksheets(“REPORTE DIARIO”).Range(“H15”).Value
    Worksheets(“SUMMARY”).Range(“K6”).Value = Worksheets(“REPORTE DIARIO”).Range(“J15”).Value
    Worksheets(“SUMMARY”).Range(“L6”).Value = Worksheets(“REPORTE DIARIO”).Range(“L15”).Value
    Worksheets(“SUMMARY”).Range(“M6”).Value = Worksheets(“REPORTE DIARIO”).Range(“N15”).Value
    Worksheets(“SUMMARY”).Range(“N6”).Value = Worksheets(“REPORTE DIARIO”).Range(“P15”).Value

  • OK I’m having a little trouble making a macro for a project at work. What I need help with: i have one work book and user form on sheet one to fill in information and this user form i have a button that should copy range of cells on sheet2 (S6:S9) to the (L6:L9)(all of this is on sheet2 and the samr thing should happen sheet3 to sheet9. If i could get some help i would be grateful

  • Hi Jon,

    I have a crunched data in one workbook and wanted paste it in other by using macro. Also the number of line items changes every day (increase or decrease), So help me to copy and pasting the data in main template without number mismatch.

  • Hello, nice writeup. One quick question- Instead of copying an orderly range of cells from one column, how may we copy multiple single cells & paste them into an orderly horizontal range?

    For example copying B2, F9, and Q11 yet pasting them into A1:C1
    Thank you!

  • I am trying to write VBA for the below.
    If the value in column AA is =”-” and the value in column AC is $0.00 then copy the value in column X to Column AA. My data set is ever changing so I can not set specific ranges and it may be the case where I don’t have data the satisfies the above criteria.

  • I have a workbook with a few tabs in it. I want to copy a cell with a formula in it from one worksheet and paste it into a cell in a different worksheet. When I use “PasteSpecial Paste:=xlPasteFormulas” it turns the formula cell references into REF# errors. For example:

    LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))=0
    becomes
    LEN(REF#)-LEN(SUBSTITUTE(REF#,” “,””))=0

    It’s pasting the formula into the destination worksheet, but it must think the cell reference is still related to the source worksheet…or something like that. Do you know what could be causing this? Thank you!

  • Can you tell me how to Cells from Sheets present in 4 or more Workbooks to one Final Workbook?

    It would be really helpful.

    Thank You.

  • i have 300+ excel workbooks.
    my requirement is copy specific data from all workbooks to another new workbook though VBA, can this possible,

    • Yes it is possible
      Sub Copy()
      Dim Sheet1 as worksheet
      Dim sheet2 as worksheet
      Dim book1 as workbook
      Dim book2 as workbook

      Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Copy
      Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”).PasteSpecial Paste:=xlPasteFormats

      End sub

  • Hi John,

    With a macro I am copying the last row of a table and pasting below as PasteFormulas, but when I do this, it consider the row as a ” total row” of the table. I do it in two different tables and in one it paste it okay but in the other one it considers it as a “Total Row” of the table.

    Can you help me?

    Many thanks!

  • Dim lastRow1 As Long, erow1 As Long
    lastRow1 = Worksheets(“WO_SendM”).Cells(Rows.Count, 1).End(xlUp).Row
    For i = 3 To lastRow1

    If Worksheets(“WO_SendM”).Cells(i, 9).Value = Me.Label164.Caption Then
    Worksheets(“WO_SendM”).Cells(i, 4).Copy
    Worksheets(“WO_SendM”).Cells(i, 5).Copy

    erow1 = Worksheets(“WO_Ledger”).Cells(Rows.Count, 18).End(xlUp).Row
    Worksheets(“WO_SendM”).Paste Destination:=Worksheets(“WO_Ledger”).Cells(erow1 + 1, 18)
    End If
    Next i

    Is it possible to get two cell value 4 and 5 in another cell?

  • I wanted to maintain a formatting of one of my records. In which I want to copy formats of certain cells to no. of times the user enters this will reduce the time of formatting ( which is a lot for me). Can you please make a video on that plz?

  • Hi jon,
    Thanks for your this tutorial, but i need a urgent help if possible answer to me.

    copy and paste only filter cells from one excel to other excel using VBA
    excel-vba
    I’m stuck with a problem when I try to copy filtered cells from one excel file and paste it onto another file. I am using a macro call to get data where I try to fix this given code. It is not working when I try to copy visible cells which i filtered.
    Private Sub CommandButton1_Click()

    Dim lrCD As Long
    Dim fNameAndPath As Variant
    Dim WB As Workbook
    Dim SourceWB As Workbook
    Dim WS As Worksheet
    Dim ASheet As Worksheet
    fNameAndPath = Application.GetOpenFilename(FileFilter:=”Excel Files (*.xlsx), *.xlsx”, Title:=”Seleziona il file da aprire”)

    ‘Sets the variables:
    Set WB = ActiveWorkbook
    Set ASheet = ActiveSheet
    Set SourceWB = Workbooks.Open(fNameAndPath) ‘Modify to match

    ‘Copies each sheet of the SourceWB to the end of original wb:
    For Each WS In SourceWB.Worksheets
    WS.Copy after:=WB.Sheets(WB.Sheets.Count)
    Next WS

    SourceWB.Close savechanges:=False
    Set WS = Nothing
    Set SourceWB = Nothing

    WB.Activate
    ASheet.Select

    Set ASheet = Nothing
    Set WB = Nothing

    Application.EnableEvents = True
    lastrow = Worksheets(4).Cells(Rows.Count, 1).SpecialCells(xlCellTypeVisible).End(xlUp).Row

    For i = 3 To lastrow
    Worksheets(4).Cells(i, 16).SpecialCells(xlCellTypeVisible).Copy
    erow = Worksheets(“CFF”).Cells(Rows.Count, 1).SpecialCells(xlCellTypeVisible).End(xlUp).Row
    Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 2)
    Worksheets(4).Cells(i, 16).SpecialCells(xlCellTypeVisible).Copy
    Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 3)
    Worksheets(4).Cells(i, 15).SpecialCells(xlCellTypeVisible).Copy
    Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 4)
    Worksheets(4).Cells(i, 12).SpecialCells(xlCellTypeVisible).Copy
    Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 5)
    Worksheets(4).Cells(i, 13).SpecialCells(xlCellTypeVisible).Copy
    Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 6)
    Worksheets(4).Cells(i, 18).SpecialCells(xlCellTypeVisible).Copy
    Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 1)
    Next i

    Application.CutCopyMode = False

    Sheets(4).Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete

    Sheets(2).Select

    End Sub
    Thanks in advance

  • Hey, when you copy a range, there is some copy buffer somewhere in excel, I assume as a manipulatable object in VBA. Is there a way to access this and what’s it called? I want to see if it’s a collection or array, or something that can be split into that- in other words, something that can be iterated over. I don’t want to just paste in one drop. I want to choose which cells I paste based on other criteria.

  • I have list of unique names of players in column A in sheet 1. And in sheet 2 I have data of cricket score of different matches for that particular player with same unique names. And in sheet 3 I have data of same players for football matches. Now I want to first find the name which is in the sheet 1 from sheet 2 and cut that data from sheet 2 and paste in same row of that particulate player. And find and cut the data from sheet 3 and paste it in sheet 1 on same row in next available cell. Do this task one by one for all players till cell is empty. In some cases I have multiple rows in sheet 2 and sheet 3 for the same name. In that case add a new row under that name and cut paste all the data. And every time I will update the sheet 2 and sheet 3 with the new data with new names which I will add in in sheet 1 too it will update all the data in sheet 1 by clicking a single button.

    I have tried lots of thin but still it is not working as I needed. Anybody can help me for this task…..

  • Copy from sheet1 and paste in sheet2 and repeate … also copy from sheet2 to sheet1
    I wish to copy the specific cells values from sheet1 to sheet2 and calculations will happen and copy again data from Sheet2 to sheet1

    this process i wish to repeate for multiple line items in excel.

    PLease help with VBA
    [email protected]

  • Thanks a lot for your videos.
    Kindly ask your support on below matter.
    I have two cells on of them is free nothing inside cells but the second one with different formulas and I wish to do copy paste for both of them at the same time with vba code. Could you please tell me how I do it when I want to add or insert rows (down fill).

    Your kind support and assistance would be highly appreciated.

  • Hello I am in a quandry. I dont want to relearn a language I would just like a client side page i can put a list of common responses. Click copy to add to clipboard and I will paste in closing remark. used to be simplke. looking for simpilist solution

  • if you could help with why this errors out, would be most appreciated on how to fix. The error happens starting below the line created using equal = signs. if i enter a hard range like Range(“A1:D1”) to test this, it will work, it is only when i use the Range(Cells(t, 1), Cells(t, 4)).value do i get the error:

    Sub doUNTILsample()
    Dim i As Long, t As Long

    Sheets(“Pending”).Select
    t = 1 ‘set value of t to 0
    i = Cells(Rows.Count, 2).End(xlUp).Row ‘find the value of the last occupied cell in COL B
    i = i + 1 ‘take result of last cell and add 1 to it
    ‘this gives us the first blank row below the last cell

    If Cells(i, 3) = “” Then ‘if COL C cell is empty – there are no new referrals
    MsgBox “There are no new conflict checks to Import”
    Else

    Sheets(“tempSHT”).Cells.ClearContents

    Do Until Cells(i, 3).Value = “” ‘do this loop until you find the first empty cell in COL C
    If Cells(i, 3).Value > “” Then ‘IF COL C cell is not empty
    Cells(i, 2).Value = “x” ‘change the corresponding value in COLB to “x”

    ‘===================================================
    ‘Copy the values of the selected row, COL C:F IN PENDING WORKSHEET TO
    ‘tempSHT, starting with ROW 1 (t), COL A:D
    ‘ERROR: ‘Application or Object defined error’

    Worksheets(“tempSHT”).Range(Cells(t, 1), Cells(t, 4)).Value = _
    Worksheets(“PENDING”).Range(Cells(i, 3), Cells(i, 6)).Value
    ‘===================================================
    t = t + 1 ‘ add 1 to VAR t, (if more results, copy next row down)
    i = i + 1 ‘ add 1 to VAR i, (test next row)
    End If
    Loop ‘REPEAT UNTIL NO VALUE IN CELL IN COL C

    End If

    End Sub

  • Great!!! But I want my data to copy paste from one sheet to another, once after the macro pastes data in sheet 2 it should automatically update the status in new column as “Updated”.
    Example macro has copy pasted data present in column A and B from sheet1 to sheet 2. Now after pasting the data in sheet2 I want my macro to fill the column C (Header – Status) as “Updated” automatically.

    Please help!!!

  • I’m going to pay you the biggest compliment I can in terms of documentation and explanation:

    I’m an old IBM mainframe programmer. IBM had the BEST documentation. Their language reference docs would use a minimal number of examples to explain every imaginable question. Including questions which arose while reading one of the examples. Very rarely would I need to ask another engineer.

    Microsoft, on the other hand, produces frustratingly incomplete and totally confusing docs. I NEVER use their references; I use pages and videos from smart guys on the internet. Like you. I love their products; I hate their docs.

    You, my friend, are of the IBM model…

    Brad

  • Private Sub CommandButton1_Click()
    Dim WSCOPY As Worksheet
    Dim WSDEST As Worksheet
    Dim LCOPYLASTROW As Long
    Dim LDESTLASTROW As Long

    Set WSCOPY = Workbooks(“REQUISITION.XLSM”).Worksheets(“MASTER”)
    Set WSDEST = Workbooks(“REQUISITION.XLSM”).Worksheets(“2021”)

    LDESTLASTROW = WSDEST.Cells(WSDEST.Rows.Count, “C”).End(xlUp).Offset(1).Row
    WSCOPY.Range(“A3”).Copy WSDEST.Range(“C” & LDESTLASTROW)

    LDESTLASTROW = WSDEST.Cells(WSDEST.Rows.Count, “B”).End(xlUp).Offset(1).Row
    WSCOPY.Range(“J3”).Copy WSDEST.Range(“B” & LDESTLASTROW)

    LDESTLASTROW = WSDEST.Cells(WSDEST.Rows.Count, “D”).End(xlUp).Offset(1).Row
    WSCOPY.Range(“J35”).Copy
    WSCOPY.Range(“J35”).PasteSpecial Paste:=xlPasteValues
    Worksheets(“MASTER”).Range(“J35”).Copy WSDEST.Range(“D” & LDESTLASTROW)

    Application.CutCopyMode = False

    Range(“A3:D6”).Select
    Selection.ClearContents
    Range(“G5:H5”).Select
    Selection.ClearContents
    Range(“J3:K3”).Select
    Selection.ClearContents
    Range(“A12:J34”).Select
    Selection.ClearContents
    Range(“J1”).Value = Range(“J1”).Value + 1
    End Sub

    This is my Macro that I have set-up. My issue is that when I run the macro it then deletes my formula on my “Master” sheet that I have in J35 even though I am only asking it to copy the value to my other sheet. All of my commands I want to happen do but I also need my Master sheet to retain the formula in that cell for the next user. Could you please help me figure out how to fix this?

    Thank you!

  • I have a file that needs to do this but my dashboard includes drop down list, therefore the data is not always located in the same range, it needs to be dynamic. Is there a way to write a code that somehow joint vlookup to find where it has to be copied and pasted? English is not my first language sorry for the typos. I have extracted the logic in a new excel to make it simple and then reply on the main file but still without finding the way for it to work.

  • how i can create Radio Button to To choose which customer on the table then button to copy this customer info (name, phon, …..) from table in sheet1 and paste in specific cells in the sheet2 (invoice)
    thank you

  • I’m trying to make a command button to copy the row i have highlighted and paste that data into the next empty 10 rows under it. Is this possible?

  • Thanks for you help
    Please, how to add a number to a another sheet example sheet1 A1 is added to Sheet2 A1 and the Anewer will be at Sheet2 A1 (Sheet1 A1 =1, Sheet2 A1 = 1, sheet1 A1 + Sheet2 A1 = Sheet2 A1 2

  • Beginner and am able to make my first macro. Right on spot!
    No time wastage, very helpful. Thanks so much

  • Hai sir
    I have the text, so I need to find the some special character and copy and paste the next column , how can I do in vba
    For example,
    Lakshmanan is the text, I want to fine only laksh and copy that and paste the next column

  • I downloaded “VBA Copy Paste Methods.xlsm” and found it very helpful. I was impressed, too, by the documentation shown on sheet “Cover”. Please let me know how you are able to hide all columns to the right of column “P” and all rows below row 53.
    Thank you.

  • How to record cell value change in sheet 1 to a column with fill down in sheet so the value change is recorded each time?

  • I have a spreadsheet to calculate and track my work hours.
    In the top of the sheet, I enter my start time in E4 and my end time in G4. K4 contains the difference in HH:MM format. I then use the mod function to change the HH:MM from K4 to display the decimal equivalent in K5.
    I’m trying to automate the lower half of the sheet that tracks my hours in decimals to be totalled weekly in Col K. I want a macro that I can run when I select the day of the week in the lower rows that will insert the value of the calculation stored in K5 above.
    Any examples seem to be using a hard coded destination for the paste special value command. I want to select the day to apply that value to dynamically and then run the macro to paste into it.
    I hope I have explained this enough for you to understand what I’m trying to do. If not, please let me know.
    Thanks so much!
    Dave L.

  • i am trying to create a simple macro that would copy from one sheet, and past to any selected cell on a different sheet. Can someone provide and example.

    Thank You

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter