This article demonstrates several VBA macros, they will save you time if you have lots of worksheets. The first macro copies worksheets from the active workbook to new workbooks, each worksheet becomes a new workbook.

The second VBA macro copies selected worksheets to new workbooks, each worksheet becomes a new workbook. The third VBA macro copies worksheets from all open workbooks and collects them all to a single master workbook.

The last macro names the created worksheets based on the original workbook name.

1. Copy each sheet in active workbook to new workbooks

The following macro copies each sheet in the current workbook to new workbooks.

VBA code

'Name macro
Sub CopySheetsToNewWorkbooks()

'Dimension variable and declare data types
Dim SHT As Worksheet

'Iterate through worksheets in active workbook
For Each SHT In ActiveWorkbook.Worksheets

'Copy worksheet to a new workbook
SHT.Copy

'Continue with next worksheet in acteive workbook
Next

'Stop macro
End Sub

Back to top

2. Copy selected sheets to new workbooks

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

VBA code

'Name macro
Sub CopySelectedSheetsToNewWorkbooks()

'Dimension variable and declare data types
Dim AW As Window

'The SET statement saves the active window as an object reference to variable AW
Set AW = ActiveWindow

'Iterate through selected worksheets based on active window
For Each SHT In AW.SelectedSheets

'The NewWindow method creates a new window based on the active window using an object reference saved to variable TempWindow
Set TempWindow = AW.NewWindow

'Copy worksheet to a new workbook
SHT.Copy

'Close the newly created window
TempWindow.Close

'Continue with next worksheet
Next

'Stop macro
End Sub

Back to top

3. Copy sheets in every open workbook to a master workbook

This macro copies all sheets in all open workbooks to a singlemaster workbook.

'Name macro
Sub CopySheetsToMasterWorkbook()

'Dimension variables and declare data types
Dim WBN As Workbook, WB As Workbook
Dim SHT As Worksheet

'Create a new workbook and save an object reference to variable WBN
Set WBN = Workbooks.Add

'Iterate through all open workbooks
For Each WB In Application.Workbooks

'Check if workbook name of object variable WB is not equal to name of object variable WBN
If WB.Name <> WBN.Name Then

'Go through all worksheets in object WB
For Each SHT In WB.Worksheets

'Copy worksheet to workbook WBN and place after the last worksheet
SHT.Copy After:=WBN.Sheets(WBN.Worksheets.Count)

'Continue with next worksheet
Next SHT

'End of If statement
End If

'Continue with next workbook
Next WB

'Disable Alerts
Application.DisplayAlerts = False

'Delete sheet1, sheet2 and sheet3 in the new workbook WBN
WBN.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete

'Enable Alerts
WBN.Application.DisplayAlerts = True

'End macro
End Sub

Back to top

3.1 Add workbook name to sheets

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

SHT.Copy After:=WBN.Sheets(WBN.Worksheets.Count)

in the macro above.

Back to top

3.2 Where to put the macros?

1. Press Alt+ F11 to open the Visual Basic Editor.
2. Select your workbook in the Project Explorer window.
3. Press with left mouse button on "Insert" on the menu.
4. Press with left mouse button on "Module" to create a module.
5. Paste code to module.
6. Return to Excel.

Back to top

Recommended reading

10 ways to reference Excel workbooks and sheets using VBA

Get the Excel file

Copy-each-sheet-in-a-workbook-to-new-workbooks.xlsm

Back to top