COMBINING WORKBOOKS

[lmarstin]

I have 32 workbooks with 3 worksheets each. Each worksheet in each workbook
is named the same. I need to combine all 32 workbooks into one files with
each of the 3 sheets moved. This will make for a total of 96 sheets. I
really don't want to have to open each file and copy the worksheets to a
master sheet.

 

[cush]

Try something like:

Option Explicit

Sub CopySheets()
Dim Wb As Workbook
Dim i As Integer
Dim j As Integer

On Error Resume Next
''GET THE FIRST WB
Set Wb = Workbooks.Open(Dir(ThisWorkbook.Path & "\*.xls"))
For i = 1 To 3
Wb.Sheets(i).Copy After:=ThisWorkbook.Sheets(1)
Next i
Wb.Close False

''GET REMAINING WBKS
For j = 1 To 32
If Wb.Name <> ThisWorkbook.Name Then
Set Wb = Workbooks.Open(Dir)
For i = 1 To 3
Wb.Sheets(i).Copy After:=ThisWorkbook.Sheets(1)
Next i
Wb.Close False
End If
Next j

Set Wb = Nothing

End Sub