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.

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

50 comments

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

  • Hello sir, maybe you can help me with a rather complex problem, can help me with that.
    In the worksheet “Menu” I have in cell “E2” I have data from 1 to 3, now I have cell “B4” and “B10” I have an ID reference to names (there are 20), what do I want now do, for example. if E2 = 1, I want to go to worksheet “1” then look at the IdNr then put the data in the right place. It contains date for “B4” = cell “C6”, “E6”, “E8”.
    for “B10” = cell “C12”, “E12”, “E14”.
    So with flea cup and a loop, maybe the next formula.
    if “E2” = 1 then
    Workbook 1
    if B4 = 1 then and B10 = 2 then
    Hlookup (B4, workbook 1 (A1: CV21), 1)
    lookup (B10; workbook 1 (A1: CV21), 3.3)
    C6 = workbook 1 (places in column 3 row 3) for each number in b4)
    E6 = workbook 1 (places in column 5 row 3) for each number in b4)
    E8 = workbook 1 (places in column 4 row 3) for each number in b4)

    if B10 = 2 and B4 = 1 then
    Hlookup (B10, workbook 1 (A1: CV1), 1)
    lookup (B10; workbook 1 (A1: CV21), 3.3)
    C6 = workbook 1 (places in column 8 (is also column 3 of “B10”) row 3) for each number in b4)
    E6 = workbook 1 (places in column 10 (is also column 5 of “B10”) row 3) for each number in b4)
    E8 = workbook 1 (places in column 9 (is also column 4 of “B10”) row 3) for each number in b4)

    So maybe it is useful to work with a loop.
    I do not know if this formula is correct.
    It would be useful to (search on both B4 and B10) than put the data in the right place, is this possible?

  • Hi JON ACAMPORA,
    Firstly of all , thanks for code to help my project.
    But i have 3 data to copy and paste it to other workbook.
    Can you help me to share the coding if i want to copy more than 2 data in 1 workbook.

    Thanks.

  • Sub Copy_PasteSpecial_Method()

    ‘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

    {Note: ofter that i want to save the workbook file at “E:\myinvoices\ with invoice number autosave}
    End Sub

  • Merhaba from turkey hello,

    İki bilgisayar arasında kopyalama yaparken özel bir işlem gerekir mi?

    Çok güzel yöntemlerle anlatmışsın.

  • I think I am missing something. I took this out of your video on VBA assumptions above and it doesn’t work unless it is in the workbook ReconcileTest.xlsm: It gives me a “Run-time error ‘9’: Subscript out of range”

    Workbooks(“ReconcileTest.xlsm”).Worksheets(“Data”).Range(“Q1”).Value = 200

    I also tried this and it only works if I change New Data to ReconcileTest when it is in the ReconcileTest file:

    Workbooks(“New Data.xlsm”).Worksheets(“Data”).Range(“A1:B20”).Copy
    Workbooks(“ReconcileTest.xlsm”).Worksheets(“Data”).Range(“O1:P20”).PasteSpecial

    In both cases both files are open. Any suggestions? Thanks

  • I have Excel (xlsm) with application written in VBA. I need to open another excel workbook (xlsx file) online on OneDrive. When I open thru ‘Set ww = Workbooks.Open(“https://..’ it always opens on local machine so other users on onedrive cannot access it while my program is using it. I used the following command to open online
    Set oExcel = New Excel.Application
    Set oWB = oExcel.Workbooks.Open(“https://…..”)
    and could get number of rows by :-
    oWBLR = oWB.Sheets(“Customers”).Cells(Rows.Count, 1).End(xlUp).Row
    But when I am trying to copy by :-
    oWB.Sheets(“Customers”).Select
    Range(Cells(2, 1), Cells(oWBLR, “G”)).Select
    Selection.Copy

    It does not select or copy any cells

  • Hello Jon and Team,

    I am using the code example you have for “Pasting Below the Last Cell”, I like how it allows for the copy range to be dynamic. But instead of having the code from the copy range get pasted below the last cell in the destination range, I would like to know how the code would be modified so that copy range gets pasted into the destination sheet starting a cell “A:11?

    Thanks

  • Hello Jon and Team,

    I am using the code example you have for “Pasting Below the Last Cell”. But instead of having the code from the copy range get pasted below the last cell in the destination range, I would like to know how the code would be modified so that copy range gets pasted into the destination sheet starting a cell “A:11?

    Thanks

  • Hello Jon and the team,

    I need to copy a file as many times as the users in a list and rename the files with users names. Do you happen to have a vba code for that. I would greatly appreciate the help!!!
    Thank you and have a wonderful day
    Anna

  • Hi Jon,

    for 3rd items : Copy and Paste Data.

    How can i change the command to paste as special as values&number.
    Currently the copy and paste are copying the formula on the ranges

  • Hi Jon, Thank you so much for this tutorial! In my situation, the name of the workbook that I need to copy/paste is always different. I am wondering if it is possible to open a file browser in order to navigate to the file that I would like to copy/paste. Thank you again!

  • Hi Jon,

    I am relatively new to VBA – love your site for easy to follow tips!

    Regarding this macro, is there any way to do this where the column headings may be different? As someone noted above, I am looking to copy and paste the data from multiple sheets to make one consolidated sheet. However, the data from my source sheets comes with different column headings i.e. what is column A in one sheet may be column C in another sheet.

    • Hi Bec,
      Yes, this is definitely possible. There are a few ways to go about it depending on how the data is set up.

      If the starting Row is the same in each sheet, then you can use the WorksheetFunction.Match method. This is the same as the MATCH function you would use in Excel to lookup data and return the row or column number in a range.

      Here is an example that looks in row 5 to find the word Date. You can change that to the value in the starting cell. It then uses the lCol variable with the column number in the next line to create the copy range. The column index in the second Cells reference adds 10 to the lCol variable. You would change that to the number of columns in your data set.

      With wsCopy
        lCol = WorksheetFunction.Match("Date", .Range("A5:K5"), 0)
        .Range(.Cells(5, iCol), .Cells(lCopyLastRow, iCol + 10)).Copy
      End With
        

      If the starting row or value of the starting cell changes in every sheet, then you can use something like the Range.Find method to find the first used cell on the sheet. Here is an article that explains more about finding the first used cell.

      I hope that helps get you started. 🙂

      • Thanks, this is really helpful – I will give it a go!

        I noticed another question about looping through worksheets to do this copy and paste – would there be any way to do this if your worksheets were not sequentially numbered? Eg if you have a handful of sheets each with a discrete name string?

        Thanks 🙂

        • Hi Bec,

          Yes, there are a few ways to go about it.

          One easy way is to re-order the sheets within the workbook so they are in a group. Then just loop through those sheets.

          For i = 5 to 11

          Another option is to use an If statement for sheets that contain a specific name or phrase. The InStr (In String) function searches for a value in a string. In this case we are looking for the word “data” anywhere in the sheet name.

          Dim ws As Worksheet
          
            For Each ws In ThisWorkbook.Worksheets
            
              If InStr(1, ws.Name, "data", 1) > 0 Then
                'code here
              End If
            
            Next ws

          You could also check for a value in a cell within the sheet. Or loop through a list of sheet names that is stored on another sheet. There are a lot of options, and this is why the For Next Loop is such a powerful tool.

          I’ll do a follow-up post in the future with some of these techniques. I hope that helps get you started. 🙂

  • Hi Jon,

    Thanks for the sharing on this VBA code. it’s really helpful and i thank you so much.
    i’m a beginner in VBA code and really want to learn more as its really helpful in my daily work with excel.

    i got 1 questions regards this sharing, do i need to create are button to run the program or the program run automatically.

    Thanks Jon

  • Hi Jon,
    A useful exercise.
    Suppose I have a monthly worksheet where each sheet has differing amounts of data (all same columns) and I want to take the data from each sheet and consolidate in a new sheet within the same workbook, with no blank rows, do I just add each sheet in the variable, so

    Set wsCopy – workbooks(January.xlsm”).Worksheet (1)
    Set wsCopy – workbooks(January.xlsm”).Worksheet (2)

    etc

    Set wsCopy – workbooks(January.xlsm”).Worksheet (31)

    And just set the final destination as worksheet 32?

    Regards
    Joe

    • Hi Joe,

      Great question! The wsCopy variable is not additive like that. Instead, we have to run the macro for each sheet. However, we can use a loop to automate this.

      In the following macro I just added a few lines with a for next loop that loops through the numbers 2 to 20. You can change those numbers to correspond to your sheet numbers. You only want to include the numbers (positions) of the sheets that you want to copy from. It will be easiest if all those sheets are together, but you can add additional code to skip specific sheets with an If statement.

      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
      Dim i As Long

        For i = 2 To 20

          'Set variables for copy and destination sheets
          Set wsCopy = ThisWorkbook.Worksheets(i)
          Set wsDest = ThisWorkbook.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)
            
          'Optional - Select the destination sheet
          wsDest.Activate
          
        Next i
        
      End Sub

      In the code above the counter variable (i) is used in the Worksheets property when setting the reference for wsCopy. This is effectively what you were trying to do in your question where you listed the worksheets. The loop just does all this work for us and makes it much faster and easier.

      Here is an article on the For Next Loop for VBA that explains more about it.

      I hope that helps.

  • I’ve just finished getting the macro to copy some data to another workbook after deleting some of the rows that were already on the “copy to” workbook. Got it to work. It is very slick. Now I’ve got to find your instructions to open a workbook that is not open yet so I can copy to it.

    Thanks for your instructions. They are great and easy to follow.

    Brian

    • Thanks Brian! I’m happy to hear you got it working. There is code on the page above on how to open and close a workbook. It is also in the example file you can download above. You will just put that Workbooks.Open line at the beginning of the macro. I hope that helps.

  • Hi John,

    I took your VBA course in the summer of 2017 and got a chance to apply what I learned in March of 2018 for a contract client. The macro shortened a process which took 5 days for 20 salespeople to 1 day for 200 people. Could not have done this without your course. The assignment was extended from 3 months to 5 months as a result and gave me the opportunity to use Power Query and Power Pivot as well.

    Writing VBA code is still a challenge but at least I understand the code and that makes it a lot easier to write. The best way to learn is to be challenged. I hope I get another opportunity to write macros in my current assignment.

    You are a blessing to those who follow you on Excel Campus. Keep up the great work!

  • Hi Jon,
    Thanks for the video, it has given me some ideas about a project I have been working on, but I need some extra clues. The project involves creating a tracker of orders that are issued in excel. With the video I know that I can use the source as a template for the orders, make the changes in the template, and then save the order with the number/code assigned to it, while the template still helps me to copy the information I need in the tracker (the destination)…not 100% ideal, but an start. However, if there is any change in the order, or I want to update anything, I have to type again everything in the template and do the whole process again. How can I amend the code so that I can apply it to a different source without having to change the name every time? I will have a look at the article about avoiding duplications, to solve the other issue the amendment would bring…but I would appreciate if you could point me to the direction where I can find the answers I need about applying the code to the source I want.

  • Hi Jon,

    Greetings for the day,

    Recently I started my learning in VBA and found your videos/sources are extremely useful to new beginners like me. I was able to perform all the Operations which mentioned above. But I have a mini project and struck hoping so this file/macros will help me.

    Coming to the problem, I need to generate Reports and send them an email. so i usually get raw data and I filter the required data and copy the data and send emails. so I wanted to automate the process by running macros. However, post pulling the raw data I need to send them in a specific template/format. but the macros which you mentioned simply copies the data and pastes. It would be great if you help me. I am really struggling and hoping so i get support on this.

    • Hi Santoshlepak,

      Sorry, we don’t provide individual support or consulting services at this time. I’m planning to do a separate post in the future on how to send files with Outlook using VBA. Thanks!

  • Dear Sir, thank you for lesson it is difficult to follow instruction for time being however I will go throught slowly to learn how to copy and pase in VBA.

    Rana Jang Bahadur

  • Hi Jon and HNY,

    I’m working on this new method, particularly the Paste Below option, and my question is, does the copy cells (“A”) need to be the same as the destination cells (“D”). This is how my data is setup.

    • Hi Lola,

      Great question! No, the column references do NOT need to be the same. You can copy from any column and start the paste in another column. The results will be the same as if you do the same copy and paste in Excel manually.

      I hope that helps.

  • Hi Jon,

    Thanks so much for the video. Very helpful.
    I would also want to check if there is a code where I can copy only specific columns from one workbook and paste it to another.

    • Hi Mohammed,

      Yes, there are a few ways to go about it. You can reference multiple areas in the copy range.

      Range("A2:B9,D2:D9,E2:G9").Copy Range("J2")

      That is just an example and you will also want to add workbook and worksheet references.

      I hope that helps.

  • Thanks very much. I am new to learning Macros and very excited about it. You make learning fun by explaining it so thoroughly.

  • Thanks Jon, I have been manually Copy/Past(ing) from one workbook to another with various Special options but (aside from pasting values) whenever I change the source workbook name it changes the destination data. I would like to make copies of the source file without changing the destination data (still referencing the original name) similar to an absolute cell reference. Does the VBA reference change with a source file name change?

    • Hi Willie,
      Great question! I believe you are referring to formulas that are copied to a new workbook? You want to retain the reference to the original workbook name, even if you change the source workbook name?

      If that is correct you will want to Close the destination workbook before you change the name of the source. The references will NOT update when the destination workbook is closed. This step can be added to your macro and I provided code in the article and file to close the workbook with VBA.

      When you open the destination file again you will get a warning message saying some of the links can’t be updated. At that point you can click Edit links to update them if needed.

      I hope that helps.

  • As always, great “small-things” automation, crystal clear explained and huge time saver. Thanks so much Jon. Keep up the good work, please!!

    • Hi Nura,
      The file size should not differ from doing a copy/paste manually. Both running the macro or copy/pasting manually should produce the same result in the destination file. If your macro is copying all the cells on the sheet, instead of a specific range, then this can increase file size. In the macros above I show examples of copying specific ranges and how to find the last used cell on the sheet to only copy the used range. I hope that helps.

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