In this article, we will create a macro to copy data from a closed workbook.
We have “DataFile” containing employee data, which includes Name, Age and Domain.
We want to copy data from this file to the main file.
Before running the macro, we need to specify the file path and the file name.
We have created “OpenClosedWorkbook” macro to copy data from the closed workbook. It opens the workbook, copies data to the main workbook and then closes the previous workbook.
Set WB = Workbooks.Open(FileName)
The above code is used to create an object of the opened workbook.
The above code is used to copy all the data from the defined workbook.
The above code is used to paste the copied data to the main worksheet.
Please follow below for the code
Option Explicit Sub OpenClosedWorkbook() 'Declaring variables Dim FileName As String Dim WB As Workbook 'Disabling screen updates Application.ScreenUpdating = False 'Getting file path and file name from the textbox FileName = Sheet1.TextBox1.Value 'Open the Excel workbook Set WB = Workbooks.Open(FileName) 'Copy data from sheet1 from opened workbook WB.Worksheets("Sheet1").Range("A1").CurrentRegion.Copy 'Paste data in the macro file starting from cell A15 ThisWorkbook.Worksheets("Main").Range("A15").PasteSpecial (xlPasteValues) 'Close the workbook without saving WB.Close (False) Set WB = Nothing 'Auto adjusting the size of selected columns Selection.Columns.AutoFit 'Saving the macro file ThisWorkbook.Save End Sub
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.