Sub CombineFiles()
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
'Declare variables
Dim path As String
Dim fileName As String
Dim wkb As Workbook
Dim ws As Worksheet
Dim sheetName As String
'Set the file path. Enter User in blank
path = "C:\Users\Intern\Desktop\Test"
fileName = Dir(path & "\*.xlsx", vbNormal)
'Loop through folder, open each workbook, copy the worksheet to the active worksheet, and close the workbook
Do Until fileName = ""
Set wkb = Workbooks.Open(fileName:=path & "\" & fileName)
For Each ws In wkb.Worksheets
ws.Name = Left(fileName, InStr(fileName, ".") - 1)
ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next ws
wkb.Close False
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
End Sub
I have a folder of different workbooks and I am trying to copy them all into one. I keep getting the error "Method 'Copy' of object'_Worksheet' failed" and I'm not sure how to fix it. This error comes up on the line:
ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Thanks
Bookmarks