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 named Book1, we already have some data in it. What we want is, to copy data from Copy Dynamic Range to another workbook; Book1.

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

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

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, allowing us to copy only the selected portion. Then, we 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.

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.

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.

Download Practice Workbooks

Sample Datasets

Copied Dataset

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: Dynamic Range Based on Cell Value
• How to Use Dynamic Range for Last Row with VBA in Excel