Insert multiple Excel worksheets

How to insert multiple Excel worksheets at the same time using Excel, VBA and Shortcut methods

METHOD 1. Insert multiple Excel worksheets using the sheet option

EXCEL

Select multiple sheets > Right-click on any of the selected sheets > Insert > Worksheet > OK

1. Press and hold the Shift key and select the number of sheets that you want to insert.
Note: in this example we are inserting three new worksheets and therefore have selected three sheets.		 Select multiple sheets - Excel

2. Right-click on any of the selected sheets.
3. Click Insert.
Note: the new worksheets will be inserted in front of the sheet that you have right-clicked on. In this example the three new worksheets will be inserted in front of Sheet3, given we have right-clicked on Sheet3.		 Right-click on one of the selected sheets and select Insert - Excel

4. Select Worksheet and click OK. Select Worksheet and click OK - Excel

METHOD 2. Insert multiple Excel worksheets using the ribbon option

EXCEL

Select multiple sheets > Home tab > Cells group > Insert > Insert Sheet

1. Press and hold the Shift key and select the number of sheets that you want to insert.
Note: in this example we are inserting three new worksheets and therefore have selected three sheets.		 Select multiple sheets - Excel

2. Select the Home tab. Select Home tab - Excel

3. Click Insert in the Cells group.
4. Click Insert Sheet.
Note: the new worksheets will be inserted in front of an active sheet. In this example the three new worksheets will be inserted in front of Sheet1, given that Sheet1 is the active sheet.		 Click Insert and click Insert Sheet - Excel

METHOD 1. Insert multiple Excel worksheets using VBA with a Worksheets object

VBA

Sub Insert_Multiple_Worksheets()
'insert three new worksheets in front of an active sheet
Worksheets.Add , Count:=3

End Sub

ADJUSTABLE PARAMETERS
Number of New Worksheets to Insert: Select the number of new worksheets you want to insert by replacing the Count number. In this example we are inserting three new worksheets and have directly entered into the VBA code the number of new worksheets to be inserted.
Sheet Selection: Select the sheet in front of which you want to insert the new worksheets.

METHOD 2.   Insert multiple Excel worksheets using VBA with a Sheets object

VBA

Sub Insert_Multiple_Worksheets()
'insert three new worksheets in front of an active sheet
Sheets.Add , Count:=3

End Sub

ADJUSTABLE PARAMETERS
Number of New Worksheets to Insert: Select the number of new worksheets you want to insert by replacing the Count number. In this example we are inserting three new worksheets and have directly entered into the VBA code the number of new worksheets to be inserted.
Sheet Selection: Select the sheet in front of which you want to insert the new worksheets.

METHOD 3. Insert multiple Excel worksheets using VBA by referencing to a cell

VBA

Sub Insert_Multiple_Worksheets()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Parameters")
'insert new worksheets in front of an active sheet using a cell reference that captures a value that represents the number of new worksheets to be inserted
Worksheets.Add , Count:=ws.Range("A1").Value

End Sub

PREREQUISITES
Worksheet Name: Have a worksheet named Parameters.
Number of New Worksheets to Insert: Have the value that represents the number of new worksheets to be inserted captured in cell ("A1") in the Parameters worksheet. Using this method will allow for a more dynamic way of changing the number of new worksheets to insert.
ADJUSTABLE PARAMETERS
Number of New Worksheets to Insert: Select the number of new worksheets you want to insert by changing the number in cell ("A1") in the Parameters worksheet. You can also change the cell and worksheet references in the VBA code to any cell and worksheet in the workbook that don't conflict with the VBA code.
Sheet Selection: Select the sheet in front of which you want to insert the new worksheets.
ADDITIONAL NOTES
Note 1: The Worksheets.Add component of the VBA code can also be written with a Sheets object (e.g. Sheets.Add), the same as shown in Method 2.

Insert multiple worksheets using a Shortcut

SHORTCUT

WINDOWS SHORTCUT

Shift
F11

NOTES
To insert multiple worksheets using this shortcut you will need to select the number of new worksheets you want to insert and then action the shortcut. In this example we are inserting three new worksheets, therefore, we need to select three sheets, as per the image in Step 1 of Method 1, and then action the shortcut.

Explanation about how to insert multiple worksheets

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to insert multiple worksheets at the same time using Excel, VBA and Shortcut methods.

Excel Methods: This tutorial provides two Excel methods that can be applied to insert multiple worksheets. The first method uses the sheet option whilst the second method uses the ribbon option. Both of the methods can be accomplished in four steps.

VBA Methods: Using VBA you can insert multiple worksheets at the same time by referencing to a Worksheets or Sheets object. You can select the number of new worksheets to insert by directly entering the number into the VBA code or include reference to a cell that captures the value that represents the number of new worksheets to insert. By referencing to a cell that captures the value that represents the number of new worksheets to insert is a more dynamic approach. This will allow you to change the number in a single cell that represents the number of new worksheets to insert, before running the VBA code. The method that has the number directly entered into the VBA code would require you to change the number in the VBA code every time you want change the number of new worksheets to insert.

Shortcut Method: Using a Shortcut you can insert multiple worksheets in front of an active sheet by selecting the number of worksheets you want to insert and actioning the shortcut.

ADDITIONAL NOTES
Note 1: When the VBA code excludes reference to a specific sheet before or after which to insert new worksheets, new worksheets will be inserted in front of an active sheet.
Note 2: Using the ribbon or sheet option, the new worksheets will be inserted in front of an active sheet.

RELATED TOPICS

Related Topic Description Related Topic and Description
Insert an Excel worksheet How to insert a single Excel worksheet using Excel, VBA and Shortcut methods
Insert an Excel worksheet as the first sheet How to insert a single Excel worksheet as the first sheet using Excel, VBA and Shortcut methods
Insert an Excel worksheet as the last sheet How to insert a single Excel worksheet as the last sheet using Excel and VBA methods
Insert an Excel worksheet after a specific sheet How to insert a single Excel worksheet after a specific sheet using Excel, VBA and Shortcut methods
Insert an Excel worksheet before a specific sheet How to insert a single Excel worksheet before a specific sheet using Excel, VBA and Shortcut methods