Insert multiple Excel worksheets before a specific sheet

How to insert multiple Excel worksheets before a specific sheet using Excel, VBA and Shortcut methods

METHOD 1. Insert multiple Excel worksheets before a specific sheet using the ribbon option

EXCEL

Select multiple sheets, including the sheet before which you want to insert new worksheets > Activate the sheet before which you want to insert new worksheets > 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.
2. Activate the sheet before which you want to insert new worksheets.
Note: make sure to select and activate the sheet before which you want to insert new worksheets. In this example we are inserting new worksheets before the Data sheet, therefore, we have selected and activated the Data sheet.

Select multiple sheets which includes the sheet before which you want to insert new worksheets

3. Select the Home tab.

4. Click Insert in the Cells group.
4. Click Insert Sheet.

METHOD 2. Insert multiple Excel worksheets before a specific sheet using the sheet option

EXCEL

Select multiple sheets , including the sheet before which you want to insert new worksheets > Right-click on sheet before which you want to insert new worksheets > Insert > Worksheet > OK

1. Press and hold the Shift key and select the number of sheets that you want to insert.
Note: make sure to select the sheet before which you want to insert new worksheets. In this example we are inserting new worksheets before the Data sheet, therefore, we have selected the Data sheet.

2. Right-click on the sheet before which you want to insert new worksheets.
Note: in this example we are inserting new worksheets before the Data sheet, therefore we have right-clicked on the Data sheet.
3. Click Insert.

Right-click on the sheet before which you want to insert new worksheets and select Insert - Excel

4. Select Worksheet and click OK.

METHOD 1. Insert multiple Excel worksheets before a specific worksheet using VBA

VBA

Sub Insert_Multiple_Worksheets_Before_a_Specific_Worksheet()

'insert three new worksheets before the Data worksheet
Worksheets.Add Before:=Worksheets("Data"), Count:=3
'the 3 in Count:=3 represents the number of new worksheets that will be inserted
'replace the Count number with the number of new worksheets you want to insert

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

PREREQUISITES
Worksheet Name: Have a worksheet named Data.

ADJUSTABLE PARAMETERS
Number of New Worksheets to Insert: Select the number of new worksheets you want to insert, before a specific worksheet, by replacing the Count number. In this example we are inserting three new worksheets, before the Data worksheet, and have directly entered, into the VBA code, the number of new worksheets to be inserted.
Worksheet Selection: Select the worksheet before which you want to insert new worksheets by changing the Data worksheet name in the VBA code to any worksheet in the workbook.

METHOD 2. Insert multiple Excel worksheets before a specific sheet using VBA

VBA

Sub Insert_Multiple_Worksheets_Before_a_Specific_Sheet()

'insert three worksheets before the Data worksheet
Sheets.Add Before:=Sheets("Data"), Count:=3
'the 3 in Count:=3 represents the number of new worksheets that you want to insert.
'replace the Count number with the number of new worksheets you want to insert.

End Sub

OBJECTS
Sheets: The Sheets object represents all of the sheets in a workbook, including worksheets and chart sheets.

PREREQUISITES
Sheet Name: Have a sheet named Data.

ADJUSTABLE PARAMETERS
Number of New Worksheets to Insert: Select the number of new worksheets you want to insert, before a specific sheet, by replacing the Count number. In this example we are inserting three new worksheets, before the Data worksheet, and have directly entered, into the VBA code, the number of new worksheets to be inserted.
Sheet Selection: Select the sheet before which you want to insert new worksheets by changing the Data sheet name in the VBA code to any sheet in the workbook.

METHOD 3. Insert multiple Excel worksheets before a specific worksheet from a list using VBA

VBA

Sub Insert_Multiple_Worksheets_Before_a_Specific_Worksheet()

'assign the worksheet name to wsName which will represent a worksheet before which new worksheets will be inserted
For Each wsName In Worksheets("Parameters").Range("C2")

'insert three new worksheets before the worksheet that is assigned to wsName
Worksheets.Add Before:=Worksheets(wsName.Value), Count:=3
'the 3 in Count:=3 represents the number of new worksheets that will be inserted
'replace the Count number with the number of new worksheets you want to insert

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Worksheet Name: Have a worksheet named Parameters.
Worksheet Selection: Cell C2 in the Parameters worksheet needs to be populated with the name of a worksheet before which you want to insert new worksheets.

ADJUSTABLE PARAMETERS
Number of New Worksheets to Insert: Select the number of new worksheets you want to insert by replacing the Count number.
Worksheet Selection: Select the worksheet before which you want to insert new worksheets by changing the Data sheet name in the VBA code to any sheet in the workbook.

ADDITIONAL NOTES
Note 1: The Worksheets object can also be replaced with a Sheets object similar to what is presented in Method 2.

METHOD 4. Insert multiple Excel worksheets before a specific sheet by referencing to a cell using VBA

VBA

Sub Insert_Multiple_Worksheets_Before_a_Specific_Worksheet()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Parameters")

'insert new worksheets before the sheet using a cell reference that captures a value that represents the number of new worksheets to be inserted
Worksheets.Add Before:=Worksheets("Data"), Count:=ws.Range("A1").Value
'this example assumes that cell ("A1") in the Parameters worksheet holds a value that represents the number of new worksheets to be inserted

End Sub

PREREQUISITES
Worksheets Names: Have two worksheets named Parameters and Data.
Minimum Number of Worksheets: Have at least two worksheets, named Parameters and Data.
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.
Worksheet Selection: Select the worksheet where you hold the name of the sheet before which you want to insert new worksheets by changing the Data worksheet name in the VBA code.

ADDITIONAL NOTES
Note 1: The methodology of referencing to a cell that captures the value that represents the number of new worksheets to insert can also be applied against Method 1 and 3.

Insert multiple worksheets before a specific sheet using a Shortcut

SHORTCUT

WINDOWS SHORTCUT

Shift

F11

NOTES
To insert multiple worksheets before a specific sheet using this shortcut you will need to have selected the number of new worksheets you want to insert before the sheet that you want to insert the new worksheets and then action the shortcut.
This method will not work if there aren't enough worksheets before the sheet that you want to insert new worksheets. For example, if a workbook has a total number of three worksheets and you want to insert four new worksheets before the second worksheet, you will not be able to achieve this given there is only one worksheet that you can select before the second worksheet. Therefore, you will only be able to insert one worksheet before the second worksheet by using this method.

Explanation about how to insert multiple worksheets before a specific sheet

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to insert multiple worksheets before a specific sheet using Excel, VBA and Shortcut methods.

Excel Methods: Using Excel you can insert multiple worksheets before a specific sheet with a ribbon or sheet option.

VBA Methods: Using VBA you can insert multiple worksheets before a specific sheet by referencing to a Worksheets or Sheets object.

You can also enter the sheet's name, before which you want to insert the new worksheets, directly into the VBA code or reference to a cell which contains the name of the sheet, before which you want to insert the new worksheets.

In addition, you can also directly enter into the VBA code the number of worksheets that you want to insert or reference to a cell that contains a value that represents the number of new worksheets you want to insert.

It is our preference to apply the later method (cell reference) given that it offers the ability to change the number of worksheets to insert by simply changing the value that is in the cell that is referenced in the VBA code, instead of needing to directly change the VBA code every time you need to change the number of new worksheets to insert.

Shortcut Method: Using a Shortcut you can insert multiple worksheets before a specific sheet by selecting the number of new worksheets you want to insert before the sheet that you want to insert the new worksheets and actioning the shortcut.

ADDITIONAL NOTES
Note 1: Using the ribbon or sheet option, the new worksheets will be inserted in front of the active sheet.