MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to combine multiple spreadsheets into 1 workbook

Posted by Susan Gross on May 03, 2000 6:33 AM

I need to write a macro (Using Excel 97) that combines multiple spreadsheets into one workbook.

I know that you can copy sheets to a new workbook if the workbook is open and I would like to find a way to automate that process.

Thanks for your help,

Susan Gross

Posted by Ivan Moala on May 04, 2000 4:44 AM

Susan try this one
(NB: untested as it is a mode on an earlier Q)

Sub CopySheets()
Dim WBk
Dim ActWBk as String

ActWBk = Thisworkbook.Name
Application.DisplayAlerts = False
Application.ScreenUpdating = False

For Each WBk In Application.Workbooks
If WBk.Name <> ActWBk Then
ActiveSheet.Copy After:=Workbooks(ActWBk).Sheets(1)
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub