Bottom Line: Learn how to use VBA macros to copy & paste data from one Excel workbook to another, including adding data to the bottom of an existing range or replacing data.

Skill Level: Intermediate

Download the Excel Files

Follow along with the video above using the same Excel files that I use. You can download them by clicking below. Here's the workbook that I copy data from in my example:

New-Data.xlsx (12.2 KB)

And here's the workbook that I copy data to.  This is the one that has all the macro code in it:

Reports.xlsm (22.0 KB)

Copy Data from One Workbook to Another Using Excel Macros

There are a few ways to copy & paste data with VBA. We are first going to use the Range.Copy method. This allows us to perform the entire action in one line of code.

  Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy _
    Workbooks("Reports.xlsm").Worksheets("Data").Range("A2")

The Range.Copy method has an optional Destination parameter that allows us to specify the range we want to paste to.

We reference the source workbook, worksheet, and range that we want to copy from. For the Destination parameter we reference the destination workbook, worksheet, and the beginning cell of the range to paste to.

Copy Data to Another Workbook Using Macros

The Range.Copy method does a regular copy and paste that includes formatting and formulas. If you just want to paste values, there is an example below.

Important Points to Remember

When using this macro to copy data from one workbook to another, keep these points in mind.

  • You must reference the correct file extension in the Workbooks property (see video above for details).
  • Workbooks do not have to be macro enabled for this to work.
  • This code can be stored in a separate workbook, such as your Personal Macro Workbook, if you choose. (Learn how to create a Personal Macro Workbook here.)
  • You do not need to select or activate the workbooks, worksheets, or even ranges first. This is because the code already specifies those details.
  • Both workbooks must be open when using this code. But the process of opening and closing workbooks can be automated with more code:
Sub OpenWorkbook()
'Open a workbook

  'Open method requires full file path to be referenced.
  Workbooks.Open "C:\Users\username\Documents\New Data.xlsx"
  
  'Open method has additional parameters
  'Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
  'Help page: https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open

End Sub


Sub CloseWorkbook()
'Close a workbook

  Workbooks("New Data.xlsx").Close SaveChanges:=True
  
  'Close method has additional parameters
  'Workbooks.Close(SaveChanges, Filename, RouteWorkbook)
  'Help page: https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.close
  
End Sub

PasteSpecial Method to Paste Values, Formats, etc.

When pasting data into the destination workbook using VBA, you can also use any of the normal Paste Special features.

There is an example macro below. You'll notice that my example uses the PasteValues type, but you could also use PasteFormulas, PasteFormats, or any of the other PasteSpecial options available. Here is a list of the PasteTypes.

  'Copy range to clipboard
  Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy
  
  'PasteSpecial to paste values, formulas, formats, etc.
  Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
  

To learn more about PasteSpecial options, check out my video series on Copy and Paste with VBA.

Pasting Below the Last Cell

Sometimes the size of your data ranges in the source and destination files will change every time you run the macro. For example, you may have a daily task of adding new entries from an exported sheet to a master list in another workbook.

Copy Data to Another Workbook below existing entries

In that case, you'll want to add the new entries directly below the last entry on your destination sheet. To do that, you can use the following macro.

Sub Copy_Paste_Below_Last_Cell()
'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  'Set variables for copy and destination sheets
  Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
  Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
    
  '1. Find last used row in the copy range based on data in column A
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    
  '2. Find first blank row in the destination range based on data in column A
  'Offset property moves down 1 row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

  '3. Copy & Paste Data
  wsCopy.Range("A2:D" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)

This code pastes your source data just below the existing destination sheet data.

In addition to finding the last row in a range or sheet, you can find the last column or cell as well. Checkout my post and video on 3 ways to find the last used row or column to learn more.

Clearing the Destination Range Before Pasting

Instead of adding to a list in your destination range, you may prefer to clear the existing range before pasting the new data. You can do that with this macro.

Sub Clear_Existing_Data_Before_Paste()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
  Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
    
    '1. Find last used row in the copy range based on data in column A
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
      
    '2. Find first blank row in the destination range based on data in column A
    'Offset property moves down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
    
    '3. Clear contents of existing data range
    wsDest.Range("A2:D" & lDestLastRow).ClearContents

    '4. Copy & Paste Data
    wsCopy.Range("A2:D" & lCopyLastRow).Copy _
      wsDest.Range("A2")

End Sub

Running that macro will remove any existing data in the destination range before inserting the data from the source worksheet.

Alternative Code for Copying Data to Your Current Workbook

I wanted to also present to you a slightly different option for your macro. Instead of identifying the destination workbook by name, you can use the ThisWorkbook property. This can be done as long as the macro is stored in the destination (or source) workbook.

By doing this, you avoid having to change the code in the event you change the file name for your destination workbook. Here is the VBA code that uses ThisWorkbook.

  Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy _
    ThisWorkbook.Worksheets("Data").Range("A2")

This reminds me that VBA will always assume that the macro you want to run applies to the active workbook if you don't specify a workbook in each line of code. I talk about that critical assumption and other important points about running VBA code in this video on VBA Assumptions.

Copy Paste Between Sheets in Same Workbook

You can modify any of the examples above to copy & paste between sheets in the same workbook. Just use the same workbook reference for the copy and destination ranges. Here is an example.

  'Copy range to clipboard
  Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy
  
  'PasteSpecial to paste values, formulas, formats, etc.
  Workbooks("New Data.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
  

You won't always need to specify the workbook, but it is a good habit to get into. Otherwise, VBA makes assumptions that can get you in trouble.

Conclusion

I hope these tips and macros help save you time when copying data between workbooks. Automating this boring task will help prevent errors and make it easy for others to update your reports.

Please leave a comment below with any questions or suggestions. Thank you! 🙂

97 comments

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

  • I love the details in the video. The only thing that I am struggling with is that the range varies in number of rows down a column. A2 is known but B ending is unknown and varies since the data is being pulled from a report that I run. How would I change that from the example shown in the video?
    currently I am using this formula
    Workbooks(“Raw Bulk Upload report.xlsx”).Worksheets(“ETPP Bulk Upload”).Range(“f4:H” & lCopyLastRow).Copy _
    Workbooks(“ETPP Bulk Upload List.xlsm”).Worksheets(“TCExport”).Range(“B2”)

  • Hi, I need to copy just one specific cell from the new data sheet, and copy and paste the value into the last row of a specific column on the last day of each month. So copy D7 (on the last day of each month), paste the value below the existing value from the previous month in a specific column (ex: Column D).

    I was able to copy the specific cell in the form of the value, but then I have issues with pasting in the column below the existing data. With the below VBA it will continually paste the cell value from D7 into the same cell on the reports tab. But I need the data from the new data sheet to paste it into the cell below the existing data in the Reports sheet.

    Sub Copy_Paste_Below_Last_Cell()
    ‘Find the last used row in both sheets and copy and paste data below existing data.

    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long

    ‘Set variables for copy and destination sheets
    Set wsCopy = Workbooks(“New-Data.xlsx”).Worksheets(“Export 2”)
    Set wsDest = Workbooks(“Reports.xlsm”).Worksheets(“All Data”)

    ‘1. Find last used row in the copy range based on data in column A
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, “A”).End(xlUp).Row

    ‘2. Find first blank row in the destination range based on data in column A
    ‘Offset property moves down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, “A”).End(xlUp).Offset(1).Row

    ‘3. Copy & Paste Data
    wsCopy.Range(“D7:D” & lCopyLastRow).Copy
    wsDest.Range(“D” & lDestLastRow).PasteSpecial xlPasteValues

    ‘Optional – Select the destination sheet
    wsDest.Activate

    End Sub

  • ‘4. Copy & Paste Data
    wsCopy.Range(“A2:D” & lCopyLastRow).Copy _
    wsDest.Range(“A2”)

    On the section of this what extra code is needed for paste special with value and format only instead of paste all.

  • How do you change the code to copy multiple files from one folder to a main file automatically? So every time a new file is uploaded to the folder it gets copied over to the main folder when you open and run the macro? Thanks

  • Good day …

    EXCELLENT!! thnX

    I need help defining the path between two workbooks that are hosted on OneDrive (365). I can save info between two workbooks on my desktop (local drive) but cannot seem to get the link when i upload the file to OneDrive

    Any help appreciated

    Regards

    Hoosen

  • Excellente! But…
    Can you help me create a macro that copies values ​​to a tab based on date? Example: In a record sheet we have values ​​from January to December by date. Depending on the registration date, the macro should copy January values ​​to January tab, February values ​​to February tab, etc.Thanks

  • please i enjoyed your videos. my name is Henry an active military guy. i have many forms that i am printing with peoples information i am able to sent the forms to print with those information but the problem is that i have to filter the name one after the other to print each form. i search for names from a list of names i have in excel run it one at a time. i am trying to see if you can help me with a macro that can go through the list one at a time and past the name for me in my pivot table run it and sent it to print.
    right now i can only past manually and use the macro and print it but i can not get the macro to get the data from another list and past on my pivot filter to run and print

  • Hi Jon,
    thanks for this tutorial, it saved me a lot of time and effort, however, i need to integrate a find function (location of a cell containing specific text) in the source sheet to define the copy range.
    can you please help me with that?

  • I’m trying to paste into another workbook as a value and enter it

    wsDest.Range(“A” & lDestLastRow).PasteSpecial Paste:=xlPasteValues

    but keep getting “Expected: End of statement”.

    Please could someone help?

  • Hi
    enjoyed how you have simplified your process.
    can you help me identify the mistake here. I am very new in VBA writing. email is

    Private Sub unpaidInvoices()

    Dim i, LastRow
    LastRow = Sheets(“Booking”).Range(“C111” & Rows.Count).End(xlUp).Row

    Sheets(“unpaidInvoices”).Range(“A2:2000”).ClearContents

    For i = 2 To LastRow

    ‘E contains the data to transfer
    If Sheets(“Booking”).Cells(i, “O”).Value = “No” Then

    Sheets(“Booking”).Cells(i, “O”).Copy
    Destination:=Sheets(“unpaidInvoices”).Range(“O” & Rows.Count).End(xlUp).Offset(1)

    End If
    Next i
    End Sub

  • The Explanation is so helpful. Thank you a lot.
    I have one question though. Instead of clearing the data, how can I write a code that would check the presence of the same data by using unique values e.g date and product (in the current case) and alert the user with a msg “Records with similar identifiers are available in the database”?

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

  • Thank you for the VBA macro. It worked. Now how can I adjust so that it can copy values from multiple worksheets to a single worksheet from the same Excel file

  • I have a reservations spreadsheet that I need to transfer data to to another sheet if a cell is not empty using an ActiveX button called TransferNoShows.

    I need the following to happen when I press the button:

    In Spreadsheet 1 If a cell in I3-I39 is not empty Transfer (append) Data in Cells D, E, F, and G in that row to Columns B, C, D, and E respectively in Spreadsheet 2 then place today’s date in column A of spreadsheet 2. Then Delete the data in Spreadsheet 1 Column I ONLY.

    In Spreadsheet 1 If a cell in S3-S39 is not empty Transfer (append) Data in Cells N, O, P, and Q in that row to Columns B, C, D, and E respectively in Spreadsheet 2 then place today’s date in column A of spreadsheet 2.Then Delete the data in Spreadsheet 1 Column S ONLY.

    Thank you in advance!

  • Is there a VBA code to copy multiple cells data from one workbook to paste into one cell in antoher workbook?
    For example, I am trying to do the following.
    Workbooks(“753 xlsm”).Worksheets(“Saturday”).Range(“A18, A21, A23”).Copy Workbooks(“Weekly “).Worksheets(“Explanations”).Range(“C23”)
    I am basically trying to copy multple comments from one workbook into another workbook on a single line/Cell.

  • looking for a macro to copy a graph from one workbook and worksheet to another workbook and worksheet while keeping the source formatting. Not able to get anything to work. Something to start from would be great. Tried recording macro and not much luck there either.

  • Hi! Thank you so much for this. It really helped me along. My problem is that I need to create new worksheets by Country then copy and paste the specific country rows of data values to individual workbooks. The workbook names would be something like “Payment Template Q419 India.xlsx” and there could be up to 40 worksheets created during that quarter. Is there a way to create a new workbook with a variable name that uses the Country name found in a specific column?

  • hello sir,
    good day to you.
    material of great help.
    i wanted to ask that i have two different workbooks of different name. In my first workbook there is data namely with header Passport No. , Id no. and arrival date. Now i want to copy data from first workbook to another which has numerous headers and data. but i have filtered it. i want to copy the missing arrival date and id numbers from the first workbook to second . How do i do that? manually i have to search one by one using passport number and then copy and paste . But how do i do it using VBA? also i have filtered the passport numbers that are missing data. Please help.

  • Dear sir,
    I need to export data from one workbook to another using criteria….Off course, your above codes are beneficial but my requirement is quite different than that. so will you help me.

    • Hi Pravin,

      I find that passing the criteria for the search parameter from worksheet “a” to worksheet “b”, then searching in worksheet “b” can be helpful. Activate the cell, then reference row, column either R1C1 or “A1” from this relative position. Copy required data into array, switch back to worksheet “a” and copy from array.

  • Hi Jon,

    Thank you very much for the information shared here!

    High quality material!

    It was of great help!

    Cheers
    Leandro

Search
Generic filters
Exact matches only
Filter by Custom Post Type

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