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

Video Tutorial

Watch on YouTube & Subscribe to our Channel

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:

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

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

211 comments

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

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

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

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

      • Hi Jon
        I have applied this VBA code to copy the data of workbook(DataExport-worksheets(d) to the bottom of workbook (Import-worksheets(p). The code is as below
        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)

        ‘Optional – Select the destination sheet
        wsDest.Activate

        End Sub
        when running the code an error is appear runtime error (9) subscript out of range. I do not know what is the problem and how can be solved.
        I prefer to send the files here but I could not,

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

  • Hi Jon,

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

    Thank you.

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

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

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

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

    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!

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

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

      • Hello, In above code I am unable to paste special

        wsCopy.Range(“A2:D” & lCopyLastRow).Copy _
        wsDest.Range(“A” & lDestLastRow).PasteSpecial Paste:=xlPasteValues

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

    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)).CopyEnd 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, 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,

    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

  • 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

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

  • 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

  • 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

  • Merhaba from turkey hello,

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

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

  • 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

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

  • 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 I have identical workbooks for each of about 15 clients in MS Teams. Each client has its own team and the workbook is saved in the same folder in each team. I need to copy one worksheet from each workbook and combine into an a single worksheet. Is there any VB for copying worksheets in teams?

  • Hi Sir,

    Suppose the file name is changing daily, how can I copy new folder file and save with new date (next business day).

    Another, how can I copy data from a changing workbook filename/location?

    Thanks for your help. 🙂

  • Hi Jon and Team,

    This Tutorial is useful indeed, I was able to execute these on my project, however I have a problem, combining “last used cell” and “paste as values” though the debugger says no error but upon running the macro it gives me a different results , mine should copy data from source up to the last used row, and paste it on another file’s last used row, for example, my Last used row is A35 if I run the code it will paste it on A192 which is not what I expect 🙁 could you help me solve my problem :(.

    Sub Copy_PasteSpecial_ValuesandLastrowused()

    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

    ‘Copy range to clipboard
    Workbooks(“New-Data.xlsx”).Worksheets(“Export 2”).Range(“A2:D” & lCopyLastRow).Copy

    ‘PasteSpecial to paste values, formulas, formats, etc.
    Workbooks(“Reports.xlsm”).Worksheets(“All Data”).Range(“A2” & lDestLastRow).PasteSpecial Paste:=xlPasteValues

    End Sub

    • Hi,
      Instead of IDestLastrow in below
      lDestLastRow = wsDest.Cells(wsDest.Rows.Count, “A”).End(xlUp).Offset(1).Row

      Use Lastrow as per below code.
      LastRow = wsDest.Cells(wsDest.Rows.Count, “A”).End(xlUp).Row + 1

  • Hi Jon,

    This is really helpful and I currently have the pasting below last cell code working, so a huge thanks for sharing.

    I would like to add an If statement where the rows are only copied over if Column “J” has Yes. I’ve tried a few different things but can’t get it to work. How do I go about adding this extra step?

  • Hi,

    I am using “Pasting Below the Last Cell” for one of my project.
    But I am getting the Error as “Run-time error ‘9’, Subscript out of range” at line “Set wsCopy = Workbooks(“New Data.xlsx”).Worksheets(“Export 2″)”.

    Could you please help in to understand how to resolve this.

  • Hi there, my VB code is:

    Dim Source As Workbook
    Dim SheetSource As String
    Dim SheetTarget As String
    Dim RangeCopy As String
    Dim RangePaste As String
    Dim File As String

    File = “C:\…xlsx”
    SheetSource = “Source”
    SheetTarget = “Target”
    RangeCopy = “K46:T71”
    RangePaste = “A10”

    Set Source = Workbooks.Open(File)

    Source.Worksheets(SheetSource).Range(RangeCopy).Copy
    ThisWorkbook.Worksheets(SheetTarget).Range(RangePaste).PasteSpecial Paste:=xlPasteValues

    I get run-time error 9, subscript out of range.
    Any help would be appreciated. Thanks!

  • HI JON,
    When i used this method { Copy_Paste_Below_Last_Cell }
    1- Its copy functions =0.
    Is there any way to make it copy the value in the cell?
    2- Can you make it copy certain cells and pasted in certain cells?

    thanks for your amazing effort .

  • Dear, I have a excel Form which the employees will fill and send me. I need to capture some of the column details in another workbook as report format. The common field is employee ID number. So if I insert employee ID in the report work book, the data from that particular work book which contains this employee ID must capture. The excel sheet name will be the employee ID number. Can you help. Thanks

  • Hi
    I am trying to copy the range of cells with formulat to a new worksheet. But getting the below error. Can you please help.

    Run-time error ‘-2147352565 (8002000b)’:
    The specific dimension is not valid for the current chart type.

    Below is my code. FYI, it is successfully opening the file mentioned in filename1. It is failing in ‘Set SourceRange’ statement.

    Sub RefTable()
    Dim sourceBook As Workbook
    Dim filename1 As String
    Dim SourceRange As Range
    Dim DestinationRange As Range
    filename1 = Cells(4, 3).Value
    Application.ScreenUpdating = False
    Set sourceBook = Workbooks.Open(filename1, , False)
    Set SourceRange = Workbooks(filename1).Worksheets(“Sheet3”).Range(“A1:T100”)
    Set DestinationRange = ThisWorkbook.Sheets(“Tables”).Range(“A1”)
    SourceRange.Copy DestinationRange
    ‘Sheets(“Tables”).Name = “Tables”
    sourceBook.Close
    Application.ScreenUpdating = True
    End Sub

    As part of this, i want to copy both the literals and fomulas present in the cells between A1 to T100 of filename1 workbook to another workbook.

    Assistance would be highly appreciated.

    Thanks
    Girish

  • Hello,

    Can you please help me with different rows to be copy and pasted in specific rows.
    For E.g:

    Copy from:

    E57:L57
    E111:L111
    E145:L145

    Paste in:

    D6:K6
    D11:K11
    D14:K14

    respectively

    I can able to copy paste 1 row by below formula:

    Sub Copy_PasteSpecial_Method()

    ‘Copy range to clipboard
    Workbooks(“Apr 19.xlsx”).Worksheets(“30 Apr”).Range(“E57:L57”).Copy

    ‘PasteSpecial to paste values, formulas, formats, etc.
    Workbooks(“Macro practice.xlsx”).Worksheets(“Data”).Range(“D6:K6”).PasteSpecial Paste:=xlPasteValues

    End Sub

    • Please note – Copy and paste are in different file.

      Please look at the formula.

      From – Apr 19.xlsx
      To – Macro practice.xlsx

  • Hi Quick question on the copy and paste code below:
    ‘4. Copy & Paste Data
    wsCopy.Range(“A2:D” & lCopyLastRow).Copy _
    wsDest.Range(“A2”)

    I see the code for copy but not the actual code for paste.. can you help me understand .. thanks a million.
    Sharon

    • Hi Sharon,
      The Copy method has an optional parameter for the Destination range.

      The underscore character is continuing on the line. However, this is really just one line of code.

      Range.Copy Destination

      You can write it in one line of code and just put a space between the word Copy and the reference to the destination range.

      The following will copy A1:A10 and paste it starting in cell B2.

      Range("A1:A10").Copy Range("B1")

      I hope that helps. Thanks again and have a nice day! 🙂

  • Hi,

    I wish to thank you for the most useful information in this article. It has helped me a great deal.

  • Hi
    I noticed that the file extensions of the two workbooks referred to in the code under the paragraph, “Copy Paste Between Sheets in Same Workbook”, are not the same. The file extension in the 2nd line of code there should also be “.xlsx”.

  • Hi, what to do if i need to copy data from one workbook to another based on condition? thank you.

  • Hello,

    I have a reference line(Row) should be inserted to multiple excels(Different name), is der a possible way to automate it using VBA

  • Hi Jon, this is really helpful, being new to VBA this simple approach you show is excellent.

    This code fits what I am trying to do, however I have 50 different “source files” from where the copy rage will come, and 01 file (1 tab) where the data will be pasted, deleting previous data every time I tun the VBA.

    How do i go about this? copy the code down as many times as “sources of data” I have ?

    Thanks,

    J

  • Dear,

    i am looking for a macro which could find and pick up data as per column heading from the raw file and paste it in template.

  • When I transfer data from one workbook to another it works but I need to paste special added to it so I only import the values and not formulas.
    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(“Production Data Log.xlsm”).Worksheets(“Data”)
    Set wsDest = Workbooks(“transfer data.xlsm”).Worksheets(“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(“Y26:AH45” & lCopyLastRow).Copy _
    wsDest.Range(“A” & lDestLastRow)

    • Hi,
      I believe this should help:

      wsCopy.Range(“Y26:AH45” & lCopyLastRow).Copy
      wsDest.Range(“A” & lDestLastRow).PasteSpecial xlPasteValues

      Note that the underscore was removed right after the ‘copy’ keyword.

      It worked for me.

      Cheers
      Leandro

Generic filters

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