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.

Table of Contents hide

Download Practice Workbook

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

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

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

Method 3: Copy Dynamic Range to a New Workbook

Practice Section

Conclusion

Download Practice Workbook

Sample Dataset

Copy Dynamic Range to another Workbook.xlsm

Copied Dataset

Book1.xlsm

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.

Similar Readings

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.

Similar Readings

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.

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.