How to Copy Data From a Closed Workbook in Excel VBA

This tutorial will explain to you the step-by-step procedure for pull or copy data from a closed workbook in Excel VBA.

Here we have two different files one file will be closed and the second one will remain open.

See the image below:-

Copy Data From a Closed Workbook
Copy Data From a Closed Workbook

Our 1st file “NewData1” contain some dataset and with the help of the VBA macro will pull that data into the “NewData2” workbook.

First, we must know what kind of data workbook “NewData1” contains.

See the image below:-

Copy Data From a Closed Workbook
Copy Data From a Closed Workbook_Image01

Now we have another workbook, “NewData2”, and see that this workbook contains nothing.

See the image below of “NewData2”:-

Copy Data From a Closed Workbook
Copy Data From a Closed Workbook_Image02

Copy Data From a Closed Workbook in Excel VBA

Now we will create a macro that will pull the data from the closed workbook (NewData1) and paste it into the open workbook (NewData2).

Macro will be created in the workbook “NewData2”.

Open the “NewData2” workbook

Go to the Developer tab and click on the Visual Basic tab, see the image below:-

Copy Data From a Closed Workbook
Copy Data From a Closed Workbook

Now click on the Visual Basic tab and go to the ‘Insert’ tab and click on “Module” from the listed options

Copy Data From a Closed Workbook
Copy Data From a Closed Workbook_Image4

Once you click on the “Module”, you will get a new module, where you will write your VBA code.

Now we will use the below VBA code to copy data from the closed workbook

Sub Copy_Data_From_Closed_Workbook()
Dim wkb1 As Workbook
Dim sht1 As Worksheet
Dim wkb2 As Workbook
Dim sht2 As Worksheet
 
Application.ScreenUpdating = False
  
Set wkb1 = Workbooks.Open(“G:\VBA Practice\New Data\NewData1.xlsm”)
Set wkb2 = Workbooks(“NewData2.xlsm”)
Set sht1 = Workbooks(“NewData1.xlsm”).Worksheets(“Sheet1”)
Set sht2 = Workbooks(“NewData2.xlsm”).Worksheets(“Sheet1”)
 
sht1.Range(“A1”).CurrentRegion.Copy
sht2.Range(“A1”).PasteSpecial xlPasteAll
 
Application.CutCopyMode = False
 
wkb1.Close True
 
Application.ScreenUpdating = True
 
End Sub
Copy Data From a Closed Workbook_Image5

See below I have explained step by step the complete VBA code.

There are two workbooks

Workbook01: NewData1

Workbook02: NewData2

Both workbooks contain one sheet as Sheet1

Workbook01: NewData1 and Sheet1

Workbook02: NewData2 and Sheet1

See the below VBA code for getting the data from the closed workbook.

In the above macro, there are 4 variables

Dim wkb1 As Workbook

‘this is for workbook NewData1

Dim sht1 As Worksheet

‘This is for Sheet1 of the workbook of NewData1

Dim wkb2 As Workbook

‘variable for the workbook of NewData2

Dim sht2 As Worksheet

‘this is for sheet1 of the workbook of NewData2

Now we will set the location or give the path to all these variables

Set wkb1 = Workbooks.Open (“G:\VBA Practice\New Data\NewData1.xlsm”) (G:\VBA Practice\New Data – this is the location of our closed workbook “NewData1”)

‘this variable will open the workbook “NewData1” and will close after its use is done.

Set wkb2 = Workbooks (“NewData2.xlsm”) ‘this variable is indicates “NewData2” workbook

Set sht1 = Workbooks (“NewData1.xlsm”).Worksheets (“Sheet1”) ‘this variable indicating the sheet1 of workbook “NewData1”

Set sht2 = Workbooks (“NewData2.xlsm”).Worksheets (“Sheet1”) ‘this variable indicates the sheet1 of workbook “NewData2”

Now we will use these variables to copy and paste data from a closed workbook.

Here we will use one of the copy and paste in Excel VBA

sht1.Range(“A1”).CurrentRegion.Copy

sht2.Range(“A1”).PasteSpecial xlPasteAll

After applying the copy and paste method, use the below command to remove cut copy mode.

Application.CutCopyMode = False

‘this code will remove cut copy mode from the workbook NewData1.

wkb1.Close True

‘this code will close the workbook “NewData1”

See the whole VBA code I have applied in this macro.

Copy Data From a Closed Workbook
Copy Data From a Closed Workbook

Now run this VBA code and see that it will pull or copy the data

We will use the above VBA code to pull or Copy the data from the closed workbook.

See in the below image of NewData2 before executing the VBA code.

Copy Data From a Closed Workbook
Copy Data From a Closed Workbook

Before executing the VBA code, the workbook “NewData2” is blank.

Now we will execute this VBA code by clicking on the circle “run” command, see in the toolbar.

And this will get our data from the closed workbook “NewData1”.

See the image below:-

Copy Data From a Closed Workbook
Copy Data From a Closed Workbook

See that all our data is copied now from the closed workbook.

So this way we can pull or copy the data from a closed workbook.

If you want to learn more about this, please visit Microsoft Office Support.

I hope you find this tutorial useful.

Please feel free to put your comments or suggestions in the comment box below.

Other Related Post

VBA Macro to Dynamically Copy and Paste from One Sheet to Another

How to Lock Scroll Area in Excel with VBA (Step By Step Guide)

How to Hide Blank Rows in Excel Using VBA Macro

How to Create Search Box in Excel (No VBA Code Required)

3 Quick Ways to Move or Copy a Worksheet into Another Workbook

3 Quick Ways to Change the Font Color in Excel VBA

How to Split Cells Diagonally in Excel (Step by Step Guide)

3 Easy Ways to Select the Visible Cells Only In Excel

How to Insert Blank Rows after Every (2nd or 3rd or nth) Row in Excel

5 Ways to Delete Blank Rows in Excel (With VBA Macro)

Leave a Comment

Your email address will not be published. Required fields are marked *