Copy worksheets in active workbook to new workbooks

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.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

1.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

1.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

1.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

1.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

2. List all open workbooks and corresponding sheets - VBA

In this post, I am going to demonstrate how to automatically create a new sheet in the current workbook and list all open workbooks and their sheets using a VBA macro.

The image above shows the new worksheet, it contains the names of the workbooks and corresponding worksheets I have currently open.

Macro code

'Name macro
Sub ListWorkbooks()

'Declare variables and data types
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Single, j As Single

'Create a new worksheet and save to object ws
Set ws = Sheets.Add

'Go through open workbooks
For j = 1 To Workbooks.Count

    'Save workbook name to cell A1 and downwards
    Range("A1").Cells(j, 1) = Workbooks(j).Name
    
    'Iterate through worksheets in given workbook
    For i = 1 To Workbooks(j).Sheets.Count
  
            'Save worksheet names to cell B1 and cells further right
            Range("A1").Cells(j, i + 1) = Workbooks(j).Sheets(i).Name

    'Continue with next worksheet
    Next i

'Continue with next workbook
Next j
End Sub

How to copy the macro to your workbook

1. Press Alt+F11 to open the VB Editor.
2. Press with right mouse button on on your workbook in the Project Explorer.
3. Press with left mouse button on "Insert" and then "Module".
4. Copy macro code.
5. Press with left mouse button on in code module window to see the input prompt.
6. Paste code to code module.
7. Return to Excel.

How to run the macro

1. Go to "Developer" tab on the ribbon.
2. Press with left mouse button on "Macros" button.
3. Select "ListWorkbooks" macro.
4. Press with left mouse button on "Run" button.

Get the Excel file

List-all-open-workbooks-and-corresponding-sheets.xlsm

Macro category

Add values to a regular drop-down list programmatically
Table of Contents Add values to a regular drop-down list programmatically How to insert a regular drop-down list Add values […]

Auto resize columns as you type
What's on this page Auto resize columns as you type - VBA Press with left mouse button on a cell […]

Automate data entry [VBA]
This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code. In […]

Excel categories

Comments (8)