How to Copy Data from One Workbook to Another Automatically Using Excel VBA

 

If you often work with multiple Excel files, manually copying and pasting data can be frustrating and error-prone. 

That’s where Excel VBA (Visual Basic for Applications) comes in handy. VBA allows you to automate repetitive tasks like transferring data from one workbook to another — all with a single click.

In this post, you’ll learn how to automatically copy data between Excel workbooks using VBA, step by step.

🧠 Why Use VBA for Copying Data?

Using VBA, you can:

• Automate daily or weekly report transfers

• Merge multiple data sources into one master file

• Reduce manual effort and human error

• Save hours every month with just one macro

---

🧩 Step-by-Step Guide

Step 1: Enable the Developer Tab

1. Open Excel → Click File → Options → Customize Ribbon

2. Check the Developer box → Click OK

This enables the Developer tab for writing and running VBA code.

---

Step 2: Open the VBA Editor

Press ALT + F11 to open the Visual Basic Editor.
Then go to Insert → Module to add a new code module.

Step 3: Add the VBA Code

Copy and paste the following code into your module:

-------------------------------------------------------------------------

Sub Dynamic_Copy_Paste()

Dim wsCopy As Worksheet

Dim wsDist As Worksheet

Dim LCopyRow As Long

Dim LDistRow As Long

Set wsCopy = Workbooks("New Data").Worksheets("Sheet1")

'set variable for 'New Data' workbook

Set wsDist = Workbooks("Mater Data").Worksheets("Sheet1")

'set variable for 'Master Data' workbook

LCopyRow = wsCopy.Cells(wsCopy.Rows.Count, 1).End(xlUp).Row

'Lst row for copied data

LDistRow = wsDist.Cells(wsDist.Rows.Count, 1).End(xlUp).Row + 1

'Last blank row for destination workbook

wsCopy.Range("A2:E" & LCopyRow).Copy wsDist.Range("A" & LDistRow)

wsCopy.Range("A2:E" & LCopyRow).ClearContents

End Sub

------------------------------------------------------------------------------------

⚙️ How the Code Works

This code will copy the data from sheet 1 and paste into new Master workbook under the already existing data.

✅ Conclusion

Using VBA to copy data between Excel workbooks is one of the easiest automations you can learn — yet it delivers massive time savings. Whether you’re preparing reports, combining data, or updating master files, this small macro can make your workflow much smoother.

Try this code today, and you’ll never copy data manually again!