Excel VBA: Copy Dynamic Range to Another Workbook

Different scenarios may arise while using Excel. You may need to copy a range from one workbook to another. We hope, after finishing the article you will know the method for Excel VBA Copy Dynamic Range to Another Workbook.
We have a dataset containing Name, Gender, Occupation, and Salary in a workbook called Copy Dynamic Range to another workbook and we want to copy these data to another workbook named Book1. In the workbook name Book1, we already have some data in it. What we want is, to copy data from Copy Dynamic Range to another workbook; Book1.

Download Practice Workbook

Sample Dataset

Copied Dataset

3 Ways to Copy Dynamic Range to Another Workbook Using Excel VBA

As we can see Book1 has already got data in it. So, we want our data to copy in its last row and update it every time we run our VBA code.

Method 1: Copy Dynamic Range to Another Workbook with WorkSheet Objects

In this method, we will create a VBA code that will help us to copy and update data into another workbook every time we run that code. So, let’s get started.

Steps:

• First, right-click on the sheet and go to View Code.

• After that, copy and paste the VBA code below.

VBA code:

Sub CopyDynamicRange()

Dim wscurrent As Worksheet
Dim wsnow As Worksheet
Dim Lastcopyrow As Long
Dim Lastdestinationrow As Long

Set wscurrent = Workbooks("Copy Dynamic Range to another workbook").Worksheets("Copy Dynamic Range")
Set wsnow = Workbooks("Book1").Worksheets("Sheet1")

Lastcopyrow = wscurrent.Cells(wscurrent.Rows.Count, 2).End(xlUp).Row
Lastdestinationrow = wsnow.Cells(wsnow.Rows.Count, 2).End(xlUp).Row + 1

wscurrent.Range("B5:E" & Lastcopyrow).Copy wsnow.Range("B" & Lastdestinationrow)

End Sub

• After that, press the F5 or play button to run the code.

Putting a space after typing wscurrent.Range(“B5:E” & Lastcopyrow).Copy will ask us to give the destination to copy. The destination to copy is defined earlier that’s why we typed wsnow.Range(“B” & Lastdestinationrow). Every time we run the code, data will be copied in Book1’s last row.

Read More: How to Use VBA to Select Range from Active Cell in Excel (3 Methods)

Similar Readings

• Excel VBA: Loop Through Columns in Range (5 Examples)
• How to Use VBA for Each Row in a Range in Excel
• VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)

Method 2: Copy Dynamic Range to Another Workbook Adding New Sheet

Now, we will see, how to copy the selected dynamic range to another workbook by adding a new worksheet.

Steps:

• At first, we will select the range we want to copy.

• First, right-click on the sheet and go to View Code.

• After that, copy and paste the VBA code below.

VBA code:

Sub Copyaddingsheet()

    Selection.Copy

    Windows("Book1.xlsm").Activate

    Sheets.Add After:=ActiveSheet

    Range("B3").Select

    ActiveSheet.Paste

    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit

End Sub

Here, we have used the SELECTION property, this will allow us to copy only the selected portion. Then, we have activated the Book1 sheet and added a sheet after the ActiveSheet. And for some presentation purposes, we have autofitted the columns

• After that, press the F5 or play button to run the code.

You can see the cells have been copied to the B3 cell because we’ve provided that reference in the code. While using this method, we have to keep Book1 open.

Read More: Excel Macro: Sort Multiple Columns with Dynamic Range (4 Methods)

Similar Readings

• Excel VBA to Loop through Range until Empty Cell (4 Examples)
• How to Convert Range to Array in Excel VBA (3 Ways)

Method 3: Copy Dynamic Range to a New Workbook

This time, we will open a new workbook and copy the dynamic range in it.

Steps:

• First, right-click on the sheet and go to View Code.

• After that, copy and paste the VBA code below.

VBA code:

Sub Addworkbook_copy()

    Application.CutCopyMode = False

    Selection.Copy

    Workbooks.Add

    Range("B4").Select

    ActiveSheet.Paste

    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit

End Sub

Similar to the previous method, we’ve used the SELECTION property and everything is almost the same, only in addition we have added a new workbook using Workbooks.Add.
You can see, we have called Application.CutCopyMode = False, this is because while adding new workbook anything may not hamper the original.

• After that, press the F5 or play button to run the code.

So, as you can see, a new workbook is created named Book3 and the data is copied into it.

Read More: How to Use Range with Variable Row and Column with Excel VBA

Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.

Conclusion

These are 3 different methods for Excel VBA Copy Dynamic Range to Another Workbook. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.

Related Articles

• Excel VBA to Select Used Range in Column (8 Examples)
• VBA to Loop Through Rows in Range in Excel (6 Examples)
• Excel VBA: Get Range of Cells with Values (7 Examples)
• How to Use VBA to Set a Range Variable to Selection in Excel (5 Methods)
• How to Use VBA to Count Rows in Range with Data in Excel (5 Macros)