Table of Contents
- Copy each sheet in active workbook to new workbooks
- Copy selected sheets to new workbooks
- Copy sheets in every open workbook to a master workbook
Copy each sheet in active workbook to new workbooks
The following macro copies each sheet in the current workbook to new workbooks.
Sub CopySheetsToNewWorkbooks() Dim SHT As Worksheet For Each SHT In ActiveWorkbook.Worksheets SHT.Copy Next End Sub
If a sheet contains a list (excel 2003) or a table (excel 2007) you can´t only use the SelectedSheets property to copy selected sheets. This article by Ron de Bruin explains a workaround: Copying Worksheets with a List or Table
- Press and hold Ctrl and select multiple sheets in your workbook
- Run CopySelectedSheetsToNewWorkbooks macro
Sub CopySelectedSheetsToNewWorkbooks() Dim AW As Window Set AW = ActiveWindow For Each SHT In AW.SelectedSheets Set TempWindow = AW.NewWindow SHT.Copy TempWindow.Close Next End Sub
Copy sheets in every open workbook to a master workbook
This macro copies all sheets in all open workbooks to a master workbook.
Sub CopySheetsToMasterWorkbook() Dim WBN As Workbook, WB As Workbook Dim SHT As Worksheet Set WBN = Workbooks.Add For Each WB In Application.Workbooks If WB.Name <> WBN.Name Then For Each SHT In WB.Worksheets SHT.Copy After:=WBN.Sheets(WBN.Worksheets.Count) Next SHT End If Next WB Application.DisplayAlerts = False WBN.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete WBN.Application.DisplayAlerts = True End Sub
Perhaps you want to know where each sheet in the master workbook came from? The following line adds the original workbook name to the sheet name.
Copy and paste this line
WBN.Sheets(WBN.Worksheets.Count).Name = Left(WB.Name, 30 - Len(SHT.Name)) & "-" & SHT.Name
after this line
in the macro above.