VBA To Merge Multiple Workbooks In To Single File With One Sheet Per Workbook

**evandewater@altalang.com** · 06-05-2013, 08:14 PM

I am trying to take multiple workbooks (each containing one worksheet) and merge them into a single workbook with each file having its own worksheet within the file.

I have the code below (I found several similar varieties on the web) but am receiving an error with the line
'WS.copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)'

It seems that the code will open each workbook from the path but that the counter is not working so nothing is getting pasted.

Any help would be much appreciated.

Eva

Code:

Sub CombineFiles()
    Dim path            As String
    Dim FileName        As String
    Dim Wkb             As Workbook
    Dim WS              As Worksheet
    Dim ThisWB          As String
     
    ThisWB = ThisWorkbook.Name
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    path = "C:\MayT1\"
    FileName = Dir(path & "*.xls")
    Do Until FileName = ""
        If FileName <> ThisWB Then
            Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
            For Each WS In Wkb.Worksheets
                
                    WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                
            Next WS
            Wkb.Close False
        End If
        FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True
     
    Set Wkb = Nothing
    Set LastCell = Nothing
End Sub

**Excel Fox** · 06-05-2013, 11:12 PM

Can't see any apparent errors. I tested the code, and it's working for me. Can you check if the master workbook doesn't have a workbook protection?

**evandewater@altalang.com** · 06-18-2013, 12:38 AM

I 'unhid' my 'personal' wb associated w/ macros. Can't seem to find the 'tools' menu to unprotect the wb.

The line of code that is giving me an error is:

WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)

The 1st wb in the c: drive is added to the workbook and then it stops.

Thanks for your suggestion.

Eva

**evandewater@altalang.com** · 06-18-2013, 01:00 AM

I don't seem to be able to 'enable macros' I've followed online instructions and saved the wb as xlsm but I still can't run any macros.

the personal wb is showing as ~personal. Can this be an issue?

Eva