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

[evandewater@altalang.com]

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



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.Cou nt)

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]

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]

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]

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

[bakerman]

Already checked this ?
Change macro security settings in Excel - Excel - Office.com (http://office.microsoft.com/en-us/excel-help/change-macro-security-settings-in-excel-HP010342232.aspx)