Combining sheets from different workbooks

[ryjones]

Hello-

I believe this is possible having searched other forums and threads, I just don't know where to start...

I have multiple XLSX files that all have data on sheet 1 (name of sheet 1 may not always be that) that I would like merged into a new XLSX workbook where each of the other single worksheets represents their own sheet in the new workbook. I've read this is possible with a VBA. Does anyone of a VBA that can accomplish this? And how to I put this into a workbook? All my files are in the same folder.

Any info or direction would be appreciated-

Thanks!

 

[tonyyy]

Copy/paste the following code into a standard vba module and save the workbook to the the same folder as your files.

See the Contextures tutorial on how to add vba code to a workbook.

Sub CombineSheetsOriginal()

Dim summary As Workbook
Dim wb As Workbook
Dim ws As Worksheet
Dim directory As String
Dim fileName As String

Application.ScreenUpdating = False
directory = ThisWorkbook.Path & "\"
fileName = Dir(directory & "*.xlsx")
Set summary = Workbooks.Add()

Do While fileName <> ""
    Set wb = Workbooks.Open(directory & fileName)
    Worksheets(1).Copy After:=summary.Sheets(summary.Sheets.Count)
    wb.Close savechanges:=False
    fileName = Dir
Loop

Application.ScreenUpdating = True
MsgBox "The dishes are done, dude!"

End Sub

Cheers,

tonyyy

 

[ryjones]

Okay, so I tried, but no luck. Here is the code I'm using:

Sub CombineSheetsOriginal()

Dim summary As Workbook
Dim wb As Workbook
Dim ws As Worksheet
Dim directory As String
Dim fileName As String

Application.ScreenUpdating = False
directory = ThisWorkbook.Path & "\"
fileName = Dir("C:\temp2\Combined.xlsx")
Set summary = Workbooks.Add()

Do While fileName <> ""
Set wb = Workbooks.Open("C:\temp2\Combined.xlsx")
Worksheets(1).Copy After:=summary.Sheets(summary.Sheets.Count)
wb.Close savechanges:=False
fileName = Dir
Loop

Application.ScreenUpdating = True
MsgBox "The dishes are done, dude!"

End Sub

Does this get put into the Workbook I want things compiled into or a different one? If I put it in that one, doesn't the extension need to be "XLSM"?

 

[tonyyy]

The code should be put into a new workbook and saved as a .xlsm in "the same folder as your files." The code, as originally written, will detect the folder path - so you do not need to make changes to the directory/path. And if you do, the code will likely fail.

The code, as originally written, will add a new workbook, and sheets from the other workbooks will be copied to it.