VBA - Combine sheets in different files into workbook

[spencerp237]

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


'Set the file path. Enter User in blank
path = "C:\Users\Person\Desktop\Folder"
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.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.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 with a bunch of excel workbooks. My goal is for this macro to loop through all of them and copy the worksheets into one workbook. When running the code, it gets caught on this:



ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

The error message is "Method 'Copy' of object'_Worksheet' failed" and I'm not sure how to fix it.

Thanks

[YasserKhalil]

Cross-Post at this link
https://www.excelforum.com/excel-programming-vba-macros/1192752-vba-combine-sheets-in-different-files-into-workbook.html

[p45cal]

and at https://www.mrexcel.com/forum/excel-questions/1014119-visual-basic-applications-combine-sheets-different-files-into-workbook.html where it appears to have been solved.