Copy worksheet from different workbook to this workbook reference problems

Question

I have two Excel Files in the same folder. The macro runs on the master workbook (wb_master). It should copy the sheet from the Data Workbook (wb_Data) to wb_master. My attempt is this:

Dim wb_name as String
Dim wb_master as Object
Dim ws_master as Object 
Dim wb_Data As Object
Dim MyPath as String
Dim DataFile as String

wb_name = ActiveWorkbook.Name    'other users could have renamed the wb, so I don't want to refer to the name with a fixed string
Set wb_master = Workbooks(wb_name)
Set ws_master = wb_master.Worksheets(1)

MyPath = ActiveWorkbook.Path
DataFile = Dir(MyFolder & "\Data_*.xlsx")

Set wb_Data = Workbooks.Open(FileName:=MyPath & "\" & DataFile)
wb_Data.Sheets(1).Copy After:=wb_master.Sheets(1)
wb_Data.Close SaveChanges:=False

The problem with this is, that in the line where it copies wb_Data.Sheets(1) it doesn't use the wb_master workbook, but the wb_data workbook as destination. I assume this is because when wb_master is called, it reevaluates the ActiveWorkbook, which at this point is wb_Data. However even though I understand, why this is happening, I can't find a solution to the problem.

Edit: This macro runs in the personal.xslb

user_9814069 · Answer

Copy Sheet From a Closed Workbook

If you run the code from the Personal.xslb, then replace ThisWorkbook with ActiveWorkbook or the appropriate workbook e.g. Workbooks("Master.xlsm").

Option Explicit

Sub CopySheet()
    
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    Dim FolderPath As String: FolderPath = dwb.Path & "\"
    
    Dim swbName As String: swbName = Dir(FolderPath & "Data_*.xlsx")
    If Len(swbName) = 0 Then Exit Sub ' file not found
    
    Dim sFilePath As String: sFilePath = FolderPath & swbName
    
    Dim swb As Workbook: Set swb = Workbooks.Open(sFilePath)
    Dim ssh As Object: Set ssh = swb.Sheets(1)
    
    ssh.Copy After:=dwb.Sheets(1) ' second sheet
    'ssh.Copy Before:=dwb.Sheets(1) ' first sheet
    'ssh.Copy After:=dwb.Sheets(dwb.Sheets.Count) ' last sheet
    
    swb.Close SaveChanges:=False

    MsgBox "Sheet copied.", vbInformation

End Sub

Copy worksheet from different workbook to this workbook reference problems

1 Answers

Copy Sheet From a Closed Workbook