Insert multiple Excel worksheets after a specific sheet
How to insert multiple Excel worksheets after a specific sheet using Excel, VBA and Shortcut methods
1. Press and hold the Shift key and select the number of sheets that you want to insert. 2. Activate the sheet to the right of the sheet after which you want to insert new worksheets. Note: make sure to select and activate the sheet to the right of the sheet after which you want to insert new worksheets. In this example we are inserting new worksheets after the Data sheet, therefore, we have selected and activated the Analysis sheet which resides to the right of the Data sheet. |
3. Select the Home tab. |
4. Click Insert in the Cells group. 5. Click Insert Sheet. |
METHOD 2. Insert multiple Excel worksheets after a specific sheet using the sheet option
EXCEL
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 to the right of the sheet after which you want to insert new worksheets. In this example we are inserting new worksheets after the Data sheet, therefore, we have selected the Analysis sheet which resides to the right of the Data sheet. |
2. Right-click on the sheet to the right of the sheet after which you want to insert new worksheets. Note: in this example we are inserting new worksheets after the Data sheet, therefore we have right-clicked on the Analysis sheet. 3. Click Insert. |
4. Select Worksheet and click OK. |
Worksheets.Add After:=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
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
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, after a specific worksheet, by replacing the Count number. In this example we are inserting three new worksheets, after the Data worksheet, and have directly entered into the VBA code the number of new worksheets to be inserted.
Worksheet Selection: Select the worksheet after 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 after a specific sheet using VBA
VBA
Sheets.Add After:=Sheets("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
Sheets: The Sheets object represents all of the sheets in a workbook, including worksheets and chart sheets.
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, after a specific sheet, by replacing the Count number. In this example we are inserting three new worksheets, after the Data worksheet, and have directly entered into the VBA code the number of new worksheets to be inserted.
Sheet Selection: Select the sheet after 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 after a specific worksheet from a list using VBA
VBA
For Each wsName In Worksheets("Parameters").Range("C2")
Worksheets.Add After:=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
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.
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 after which you want to insert new worksheets.
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 after 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 after a specific sheet by referencing to a cell using VBA
VBA
Dim ws As Worksheet
Worksheets.Add After:=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
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.
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.
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 after 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.
NOTES
To insert multiple worksheets after a specific sheet using this shortcut you will need to have selected the number of new worksheets you want to insert after 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 after 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 after the second worksheet, you will not be able to achieve this given there is only one worksheet that you can select after the second worksheet. Therefore, you will only be able to insert one worksheet after the second worksheet by using this method.
This tutorial explains and provides step by step instructions on how to insert multiple worksheets after a specific sheet using Excel, VBA and Shortcut methods.
Excel Methods: Using Excel you can insert multiple worksheets after a specific sheet with a ribbon or sheet option.
VBA Methods: Using VBA you can insert multiple worksheets after a specific sheet by referencing to a Worksheets or Sheets object.
You can also enter the sheet's name, after which you want to insert the new worksheets, directly into the VBA code or reference to a cell that contains the name of the sheet, after 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 after a specific sheet by selecting the number of new worksheets you want to insert after 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.