Excel VBA: Copy Dynamic Range to Another Workbook

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Vba copy dyanamic range to another workbook


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.

Excel Vba copy dyanamic range to another workbook auto update

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

Excel Vba copy dyanamic range to another workbook

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


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.

Excel Vba copy dyanamic range to another workbook by adding sheet

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

Excel Vba copy dyanamic range to another workbook by adding sheet

  • 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


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.

Excel Vba copy dyanamic range to another workbook adding workbook

  • 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

Excel Vba copy dyanamic range to another workbook

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.

Excel Vba copy dyanamic range to another workbook


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

Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo