37

How to Use VBA Macros to Copy Data to Another Workbook

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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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! πŸ™‚

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 37 comments
Jacquie - January 18, 2019

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!

Reply
Bec S - January 17, 2019

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.

Reply
    Jon Acampora - January 18, 2019

    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. πŸ™‚

    Reply
Faiz Samsubaha - January 17, 2019

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

Reply
    Jon Acampora - January 17, 2019

    Thanks Faiz! The macro will not run automatically unless you set it up to do so. You can run it from the VB Editor, create a button to run it, or have it run based on an event (user action). Here is a video and article on events.

    I’m also working on an article on macro buttons that will be out next week. This article and video on macro buttons for filters does explain the basics though.

    I hope that helps.

    Reply
Joe - January 17, 2019

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

Reply
    Jon Acampora - January 17, 2019

    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.

    Reply
      Joe - January 17, 2019

      Hi Jon,
      Thanks for the super speedy reply. Will try it.
      Thanks.Joe

      Reply
Brian - January 15, 2019

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

Reply
    Jon Acampora - January 17, 2019

    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.

    Reply
Len Porochnia - January 14, 2019

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!

Reply
Steph - January 14, 2019

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.

Reply
santoshlepak - January 13, 2019

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.

Reply
    Jon Acampora - January 14, 2019

    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!

    Reply
Rana - January 12, 2019

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

Reply
Lola Simmons - January 11, 2019

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.

Reply
    Jon Acampora - January 14, 2019

    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.

    Reply
Mohammed Sufiyan - January 11, 2019

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.

Reply
    Jon Acampora - January 14, 2019

    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.

    Reply
femi okodugha - January 10, 2019

Thank you. I appreciate the tips and will practice it

Reply
    Jon Acampora - January 11, 2019

    Awesome! Thanks Femi! πŸ™‚

    Reply
Sandeep Kothari - January 10, 2019

Excellent as usual with you, Jon!

Reply
Nishi - January 10, 2019

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

Reply
    Jon Acampora - January 10, 2019

    Thanks Nishi! I really appreciate the nice feedback. πŸ™‚

    Reply
Nick - January 10, 2019

Hi Jon,

congratulation for your incredible work.
I watch all the time with pleasure – you are an inspiration to me.

Thank you.

Reply
    Jon Acampora - January 10, 2019

    Thank you Nick! I really appreciate your support and that means a lot to me. πŸ™‚

    Reply
Willie Stevenson - January 10, 2019

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?

Reply
    Jon Acampora - January 10, 2019

    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.

    Reply
Jordi - January 10, 2019

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

Reply
    Jon Acampora - January 10, 2019

    Thank you for the nice feedback Jordi! I really appreciate your support. πŸ™‚

    Reply
Will DeLoach - January 10, 2019

How would you handle it if you just wanted to copy unique data to the second file.

Reply
    Jon Acampora - January 10, 2019

    Hey Will,
    Great question! Here is a post where I explain how to remove duplicates to create a list of unique values. In method #2 on that post I share a macro to remove duplicates using the RemoveDuplicates method. This can be modified to remove duplicates from one or multiple columns, just like the Remove Duplicates feature in Excel.

    That macro adds a new sheet to the workbook, copies the data to the new sheet, and then removed duplicates. You can change that to copy the data to a different workbook instead of adding the new sheet.

    I hope that helps. Thanks! πŸ™‚

    Reply
Nura - January 10, 2019

Why when I use the macros to copy paste data does it make my excel size so large??

Reply
    Jon Acampora - January 10, 2019

    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.

    Reply
Marci - January 10, 2019

Excellent time saving tips – Thanks Jon!!

Reply

Leave a Reply: