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 &LT;&GT; ActWBk Then
WBk.Activate
ActiveSheet.Copy After:=Workbooks(ActWBk).Sheets(1)
WBk.Activate
WBk.Close
End If
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Ivan