How To Update Data One Sheet To Another Sheet Use Vba In Microsoft Excel

Bottom Line: Learn how to utilize 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 to a higher place using the aforementioned Excel files that I utilize. You can download them past clicking below. Here's the workbook that I copy data from in my example:

New-Data.xlsx (12.2 KB)

And hither's the workbook that I copy data to.  This is the one that has all the macro lawmaking in information technology:

Reports.xlsm (22.0 KB)

Re-create Data from One Workbook to Another Using Excel Macros

In that location are a few ways to copy & paste information with VBA. We are beginning going to use the Range.Copy method. This allows us to perform the entire activity in one line of code.

            Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy _
    Workbooks("Reports.xlsm").Worksheets("Information").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 nosotros want to copy from. For the Destination parameter we reference the destination workbook, worksheet, and the beginning cell of the range to paste to.

The Range.Copy method does a regular copy and paste that includes formatting and formulas. If you but want to paste values, at that place is an example beneath.

Important Points to Remember

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

• You lot must reference the correct file extension in the Workbooks property (see video above for details).
• Workbooks do not have to exist macro enabled for this to work.
• This lawmaking can be stored in a separate workbook, such every bit your Personal Macro Workbook, if you choose. (Larn 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 considering the code already specifies those details.
• Both workbooks must be open when using this lawmaking. Just 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 exist referenced.              
  Workbooks.Open "C:\Users\username\Documents\New Data.xlsx"
            
  'Open method has additional parameters              
'Workbooks.Open up(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)              
'Help page: https://docs.microsoft.com/en-usa/office/vba/api/excel.workbooks.open            
            
              Terminate              Sub            
            
              Sub              CloseWorkbook()
              'Shut a workbook            
            
  Workbooks("New Data.xlsx").Close SaveChanges:=True            
            
  'Close method has additional parameters              
'Workbooks.Close(SaveChanges, Filename, RouteWorkbook)              
'Assistance page: https://docs.microsoft.com/en-us/function/vba/api/excel.workbook.close            
            
Cease              Sub            
                  

PasteSpecial Method to Paste Values, Formats, etc.

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

There is an example macro below. You'll notice that my case uses the PasteValues type, but y'all could also use PasteFormulas, PasteFormats, or any of the other PasteSpecial options available. Hither 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 than virtually PasteSpecial options, check out my video series on Re-create 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 fourth dimension you run the macro. For instance, y'all may have a daily task of adding new entries from an exported sail to a chief list in some other workbook.

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

                      Sub            Copy_Paste_Below_Last_Cell()
            'Detect the terminal 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 re-create and destination sheets              
Set              wsCopy = Workbooks("New Data.xlsx").Worksheets("Consign ii")
Fix              wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
            
    'ane. Find last used row in the copy range based on data in column A              
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").Stop(xlUp).Row
            
    '2. Find first blank row in the destination range based on information in column A              
'Offset property moves down one row              
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(one).Row
            
'3. Copy & Paste Data              
  wsCopy.Range("A2:D" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)
                  

This lawmaking pastes your source data but beneath the existing destination sheet data.

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

Immigration the Destination Range Before Pasting

Instead of calculation to a list in your destination range, you may adopt to clear the existing range before pasting the new information. You lot tin practice that with this macro.

                      Sub            Clear_Existing_Data_Before_Paste()
              Dim              wsCopy              Equally              Worksheet
              Dim              wsDest              As              Worksheet
              Dim              lCopyLastRow              Every bit              Long              
              Dim              lDestLastRow              Equally              Long            
            
Set              wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
Set              wsDest = Workbooks("Reports.xlsm").Worksheets("All Information")
            
    '1. Find concluding 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 bare row in the destination range based on data in column A              
'Kickoff property moves downwards 1 row              
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
            
    'iii. 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")
            
              Cease              Sub            
                  

Running that macro will remove whatsoever 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 lot can use the ThisWorkbook property. This can exist done as long as the macro is stored in the destination (or source) workbook.

Past doing this, you avoid having to change the code in the upshot yous 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").Re-create _
    ThisWorkbook.Worksheets("Data").Range("A2")        

This reminds me that VBA will e'er 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 most that critical supposition and other important points near running VBA lawmaking 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 Information.xlsx").Worksheets("Export").Range("A2:D9").Copy
  'PasteSpecial to paste values, formulas, formats, etc.              
  Workbooks("New Information.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
                  

Y'all won't always need to specify the workbook, simply it is a good addiction to become into. Otherwise, VBA makes assumptions that tin get you in trouble.

Determination

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

Please get out a comment below with any questions or suggestions. Thanks! 🙂

Source: https://www.excelcampus.com/vba/copy-paste-another-workbook/

Posted by: wrighttomentioun38.blogspot.com

Share this post