Combine multiple sheets with same name from seperate wbs in a folder into one workbook

[SAFC1990]

Hello All,

I want to combine a specific tab ("Summation") in all workbooks in a folder to a single workbook. I have my macro asking for the file path, a file name pattern and the tab name from each workbook. The only 2 I really need are the file path and the tab name but the middle is just something that could be useful in future use.

The only tab that is in the folder that is running the macro is "SumMaster" which also has a macro that compiles the numbers I need once all the sheets exist in a single workbook. I have roughly 200 files and this process has to be repeated yearly, so I am trying to automate it as much as possible.

I have a code to combine the workbooks but it bombs out [Runtime Error '9'] at

Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)

I have tried included On Error Resume Next but that just causes Excel to completely freeze and requires a restart.

If anyone has any suggestions on how to optimize or if they have another code that would work better, please let me know.
I have included the entire code below.

Sub CombineSheets()
 
 
    Dim sPath As String
    Dim sFname As String
    Dim wBk As Workbook
    Dim wSht As Variant
 
    Application.EnableEvents = False
    Application.ScreenUpdating = False
 
    sPath = InputBox("Enter a full path to workbooks")
    ChDir sPath
    sFname = InputBox("Enter a filename pattern")
    sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal)
    wSht = InputBox("Enter a worksheet name to copy")
    Do Until sFname = ""
        Set wBk = Workbooks.Open(sFname)
        Windows(sFname).Activate
        Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
        wBk.Close False
        sFname = Dir()
    Loop
    ActiveWorkbook.Save
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
   
End Sub

 

[tonyyy]

There might be a couple things you can try:

1. Change "Dim wSht As Variant" to "Dim wSht as String" - after all, that is what you're asking the user to enter into the InputBox. (Although, I doubt this is the error source.)
2. There might be an issue with the "ThisWorkbook" refererence, as the currently active workbook will also be ThisWorkbook. You might...
Add "Dim wb as workbook"
Add "Set wb = ThisWorkbook" at the top of your code, just after the Application.ScreenUpdating setting
Change the line causing the error to "Sheets(wSht).Copy Before:=wb.Sheets(1)"

Good luck,

tonyyy