Article updated on April 24, 2014

Table of Contents

  1. Copy each sheet in active workbook to new workbooks
  2. Copy selected sheets to new workbooks
  3. 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.

VBA code

Sub CopySheetsToNewWorkbooks()
Dim SHT As Worksheet
For Each SHT In ActiveWorkbook.Worksheets
End Sub

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

Sub CopySelectedSheetsToNewWorkbooks()
Dim AW As Window

Set AW = ActiveWindow
For Each SHT In AW.SelectedSheets
Set TempWindow = AW.NewWindow

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

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.

Recommended reading

10 ways to reference Excel workbooks and sheets using VBA

Download excel *.xlsm file

Copy each sheet in a workbook to new workbooks.xlsm