VBA To Merge Multiple Workbooks In To Single File With One Sheet Per Workbook
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
