One of the most widely used tasks that we encounter while working in Excel VBA is to copy a data range from one workbook to another. In this article, I’ll show you how you can copy a range of data from one workbook to another using Excel VBA.
Excel VBA: Copy Range to Another Workbook (Quick View)
Sub Copy_Range_to_Another_Workbook()
Book1_Name = "Workbook1"
Book2_Name = "Workbook2"
Book1_Location = "C:\Users\Default User.DESKTOP-BEHT2NG\Desktop\Rifat\4273"
Book2_Location = "C:\Users\Default User.DESKTOP-BEHT2NG\Desktop\Rifat\4273"
Book1_Sheet = "Sheet1"
Book2_Sheet = "Sheet1"
Workbooks.Open Book1_Location + "\" + Book1_Name + ".xlsx"
Worksheets("Sheet1").Range("B3:D13").Copy
Workbooks.Open Book2_Location + "\" + Book2_Name + ".xlsx"
Range("B3:D13").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
An Overview to Copy a Range to Another Workbook Using Excel VBA (Step-by-Step Analysis)
Here we’ve got a worksheet Sheet1 in a workbook called Workbook1 that contains the names, contact numbers, and email addresses of some customers of a bank in the range B3:D13.
Our objective is to copy the data set from this workbook to Sheet1 of another workbook called Workbook2.
For your better understanding, I am showing you the step-by-step analysis of the VBA code.
⧪ Step 1: Taking the Necessary Inputs
First of all, you have to take the necessary inputs to the code. The inputs include
- The name of the source workbook (Workbook1 in this example)
- The name of the destination workbook (Workbook2 in this example)
- The full location of the source workbook on the computer.
- The full location of the destination workbook on the computer.
- The worksheet name of the source workbook (Sheet1 here).
- The worksheet name of the destination workbook (Also Sheet1 here).
Book1_Name = "Workbook1"
Book2_Name = "Workbook2"
Book1_Location = "C:\Users\Default User.DESKTOP-BEHT2NG\Desktop\Rifat\4273"
Boook2_Location = "C:\Users\Default User.DESKTOP-BEHT2NG\Desktop\Rifat\4273"
Book1_Sheet = "Sheet1"
Book2_Sheet = "Sheet1"
⧪ Step 2: Opening Source Workbook and Copying the Desired Range
Next, we’ll open the source workbook (Workbook1) and copy the desired range (B3:D13) from there.
Workbooks.Open Book1_Location + "\" + Book1_Name + ".xlsx"
Worksheets("Sheet1").Range("B3:D13").Copy
Notes:
- Here I’ve assumed that the extension of both the workbooks is .xlsx. If the extension of any workbook is different (Like .xlsm or .xlsb), use that instead.
- If you want to copy all the used cells of the workbook, use the UsedRange property of VBA.
⧪ Step 3: Opening Destination Workbook and Pasting the Copied Range
Next, we’ll open the destination workbook (Workbook2) and paste the copied range there. I’ve pasted it in the range B3:D13 of the workbook. You can change it according to your needs.
Also, I’ve used the xlPasteAll property of the PasteSpecial method of VBA.
Workbooks.Open Book2_Location + "\" + Book2_Name + ".xlsx"
Range("B3:D13").PasteSpecial Paste:=xlPasteAll
⧪ Step 4 (Optional): Turning Off the CutCopy Mode
Finally, we’ll turn the CutCopy Mode to False.
Application.CutCopyMode = False
Therefore, the complete VBA code will be:
⧭ VBA Code:
Sub Copy_Range_to_Another_Workbook()
Book1_Name = "Workbook1"
Book2_Name = "Workbook2"
Book1_Location = "C:\Users\Default User.DESKTOP-BEHT2NG\Desktop\Rifat\4273"
Book2_Location = "C:\Users\Default User.DESKTOP-BEHT2NG\Desktop\Rifat\4273"
Book1_Sheet = "Sheet1"
Book2_Sheet = "Sheet1"
Workbooks.Open Book1_Location + "\" + Book1_Name + ".xlsx"
Worksheets("Sheet1").Range("B3:D13").Copy
Workbooks.Open Book2_Location + "\" + Book2_Name + ".xlsx"
Range("B3:D13").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
End Sub
Read More: Macro to Copy Data from One Workbook to Another Based on Criteria
Developing the Macro to Copy Range to Another Workbook Using Excel VBA
We’ve seen the step-by-step analysis of the code to copy a range from one workbook to another using VBA.
Now let’s see how to develop a Macro to run the code.
⧪ Step 1: Opening the VBA Window
Press ALT + F11 on your keyboard to open the Visual Basic window.
⧪ Step 2: Inserting a New Module
Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.
⧪ Step 3: Putting the VBA Code
This is the most important step. Insert the given VBA code in the module. Change the name of the workbook to the one you require.
⧪ Step 4: Running the Code
Click on the Run Sub / UserForm tool from the toolbar above.
- Both the source workbook (Workbook1) and the destination workbook (Workbook2) will open and the selected range will be copied from the source workbook to the destination workbook.
Read More: Macro to Copy and Paste from One Worksheet to Another (15 Methods)
Things to Remember
- For pasting the copied range we’ve used the xlPasteAll property of the PasteSpecial method of VBA. Along with it, there are 11 more properties of the PasteSpecial method. You can have a glance at them if you are interested.
Conclusion
Therefore, this is the process to copy a range from one workbook to another using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
Related Articles
- Excel VBA: Copy Cell Value and Paste to Another Cell
- How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
- Apply VBA PasteSpecial and Keep Source Formatting in Excel
- How to Disable Copy and Paste in Excel without Macros (With 2 Criteria)
- Copy and Paste Visible Cells Only in Excel (3 Easy Ways)
- How to Copy Merged and Filtered Cells in Excel (4 Methods)
- Copy the Same Value in Multiple Cells in Excel (4 Methods)
This is one of the best implementation of the Copy/Paste VBA method. Love how tidy the code is, defining the inputs instead of hard coding it.
Personally, I like to define the inputs on a dedicated sheet in the workbook, which allows other users to change it without opening the code at all. But an even better version that my firm use as a standard template is add a loop so that the code will read through all the inputs in the table, to copy and paste items.
Anyway, thank you for bringing this information to the masses.