Copy paste a sheet from closed workbooks using VBA I am currently using the following code to copy paste specific sheets from closed workbooks. This code opens the specified WB and copies your sheet, then closes the WB when done.
I am currently using the following code to copy paste specific sheets from closed workbooks. This code opens the specified WB and copies your sheet, then closes the WB when done. All you're left with is the copied sheet in your designated Macro-Enabled WB which you are working on.
Question: I have a naming convention as 'YYYYMMDD' as shown in the code. How can I make this adjust to say:
1) reference to a cell which contains the date of choice; so it can pull that date
2) reference to a cell which contains the text format preferred for the file extension to be copied from as '_FA_BAL.xls' for example
3) reference to a cell which will provide the renaming of the sheet name as 'BAL MMDD' for example, or as shown in the code.
??
Thanks!!
---
Sub WC_DL()
'Opens directory path for WB to copy sheet from
Workbooks.Open Filename:='C:UsersCarlosOneDriveDocuments20150814_FA_BAL.xls'
'Identifies the specific sheet name in the WB as sheet1
'Then it brings it to the active WB xlsm which is specified by name
Sheets('Sheet1').Copy After:=Workbooks('My_Current_WB_Name.xlsm').Sheets(Sheets.Count)
'Renames the copied sheet
ActiveSheet.Name = 'BAL 0814'
' Closes the WB which is copied
Workbooks('20150814_FA_BAL.xls').Close
End Sub
Extracting data from a closed file in another workbook is a common request by most of the excel user. They would like to pull or consolidate data from closed files; however, this is not possible. There is a workaround which helps to solve this problem. The solution is using Macro code.
Question: On a daily basis, I spend time in copying data from one file to another. There are two files “Open.xls” & “Closed.xls” & I want to copy data from “Closed.xls” to “Open.xls” via VBA code.
Key things to know:
- The first thing is we should know the “Usedrange” of the closed workbook i.e. “Closed.xls” within the open workbook for e.g. “Open.xls”
- We can use IF function in Usedrange of “Closed.xls” workbook within the “Open.xls” workbook & it will extract the data from “Closed.xls” workbook
- If the referencing cell is blank, #N/A is put in place. Using the specialcells method, you can then delete all the #N/A errors and change the formula to values
To start with it, we will save two excel files 1) Open.xls 2) Closed.xls in path “D:Test Folder”
Following is the snapshot of “Closed.xls” workbook:
To automatically save data from “Closed.xls” file to “Open.xls” file, we need to follow the below steps to launch VB editor
- Click on Developer tab
- From Code group, select Visual Basic
- Copy the below code in ThisWorkbook (Closed.xls)
![Excel vba copy value from closed workbook Excel vba copy value from closed workbook](/uploads/1/2/5/5/125541507/939948214.png)
- To pull the data in “Open.xls”, copy the following code in the Standard Module
- Copy the following code in ThisWorkbook (Open.xls)
Now the VBA codes are all set; all we need to do is open the file name “Open.xls”. Following is the snapshot of “Open.xls” file:
The code is not limited to cell copy from A1.usedrange; the code will pick the starting range & will work perfectly fine. The files are available for download & we recommend you to have a go.
Conclusion: With VBA code, we can automate data extraction task without opening destination workbook & we save ourselves from doing manual copying & pasting.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]