How to copy a sheet from a closed workbook using VBA?

To copy a sheet from a closed workbook using VBA, you’ll need to perform the following steps:

Open the closed workbook in the background using VBA.
Copy the sheet you want from the opened workbook to your destination workbook.
Close the opened workbook without saving any changes.

Here’s a sample VBA code that demonstrates how to achieve this:

Sub CopySheetFromClosedWorkbook()

    Dim sourceWorkbook As Workbook
    Dim destinationWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim sourceFilePath As String
    Dim sourceSheetName As String
    Dim destinationSheet As Worksheet
    
    ' Full path and filename of the closed workbook
    sourceFilePath = "C:pathtoyourfile.xlsx"
    
    ' The name of the sheet you want to copy
    sourceSheetName = "Sheet1"
    
    ' Reference to the active workbook where the sheet will be copied
    Set destinationWorkbook = ThisWorkbook
    
    ' Open the source workbook
    Set sourceWorkbook = Workbooks.Open(sourceFilePath, ReadOnly:=True)
    
    ' Reference the worksheet you want to copy
    Set sourceWorksheet = sourceWorkbook.Sheets(sourceSheetName)
    
    ' Copy the sheet to the destination workbook
    sourceWorksheet.Copy After:=destinationWorkbook.Sheets(destinationWorkbook.Sheets.Count)
    
    ' Optionally, rename the copied sheet in the destination workbook if needed
    Set destinationSheet = destinationWorkbook.Sheets(destinationWorkbook.Sheets.Count)
    destinationSheet.Name = "CopiedSheetName"
    
    ' Close the source workbook without saving changes
    sourceWorkbook.Close SaveChanges:=False

End Sub

Important Points:

sourceFilePath: Update this with the actual path to your closed workbook.
sourceSheetName: Specify the exact name of the sheet you want to copy.
destinationSheet.Name: Optionally, rename the copied sheet in the destination workbook.

Additional Notes:

Make sure the path and file names are correct, and that the file extension matches the file format (e.g., `.xls`, `.xlsx`).
The VBA macro needs appropriate permissions to access the file system. Make sure your security settings allow macros to run.
You can modify the script as per your requirements, such as copying the sheet to the beginning of the workbook or appending it as the first sheet.

facebooklinkedin