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.

VBA Copy Paste Methods.xlsm (91.7 KB)

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.

VBA Copy Paste Methods.xlsm (91.7 KB)

'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!

165 comments

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

  • 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,

    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

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

  • 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.

  • 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)
    Do you have any suggestions on how to do this?
    Thanks,
    Erika

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly