spencerp237
07-13-2017, 09:00 AM
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
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