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

SAFC1990

New Member
Joined
Sep 22, 2015
Messages
1
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
Code:
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.

Code:
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
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
 

Forum statistics

Threads
1,136,969
Messages
5,678,888
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top