Insert an Excel worksheet before the last sheet
How to insert a single Excel worksheet before the last sheet using Excel, VBA and Shortcut methods
Select the second to last sheet > Click New sheet button
| 1. Select the second to last sheet. 2. Click on the New sheet button. Note: by using the New sheet button a new worksheet will be inserted after an active sheet. |
 |
METHOD 2. Insert an Excel worksheet before the last sheet using the sheet option
EXCEL
Right-click on the last sheet > Insert > Worksheet > OK
| 1. Right-click on the last sheet. 2. Click Insert. |
 |
| 3. Select Worksheet and click OK. |  |
METHOD 3. Insert an Excel worksheet before the last sheet using the ribbon option
EXCEL
Select the last sheet > Home tab > Cells group > Insert > Insert Sheet
| 1. Select the last sheet. |  |
| 2. Select the Home tab. |  |
| 3. Click Insert in the Cells group. 4. Click Insert Sheet. |
 |
Sub Insert_Worksheet_Before_the_Last_Sheet()
'insert a new worksheet before the last sheet
Worksheets.Add Before:=Sheets(Sheets.Count)
Worksheets.Add Before:=Sheets(Sheets.Count)
End Sub
ADDITIONAL NOTES
Notes 1: Sheets.Count will count the number of sheets in the workbook.
Notes 1: Sheets.Count will count the number of sheets in the workbook.
METHOD 2. Insert an Excel worksheet before the last worksheet using VBA
VBA
Sub Insert_Worksheet_Before_the_Last_Worksheet()
'insert a new worksheet before the last worksheet
Worksheets.Add Before:=Worksheets(Worksheets.Count)
Worksheets.Add Before:=Worksheets(Worksheets.Count)
End Sub
PREREQUISITES
Minimum Number of Worksheets: The workbook must have at least one worksheet. Given a workbook can comprise either or both worksheets and/or chart sheets, and the VBA code is to insert a new worksheet before the last worksheet there must be at least one worksheet that the VBA code can refer to and insert a new worksheet before it. If a workbook comprises only chart sheets the VBA code will return an error stating "Subscript out of range".
Minimum Number of Worksheets: The workbook must have at least one worksheet. Given a workbook can comprise either or both worksheets and/or chart sheets, and the VBA code is to insert a new worksheet before the last worksheet there must be at least one worksheet that the VBA code can refer to and insert a new worksheet before it. If a workbook comprises only chart sheets the VBA code will return an error stating "Subscript out of range".
ADDITIONAL NOTES
Notes 1: Worksheets.Count will count the number of worksheets in the workbook.
Notes 1: Worksheets.Count will count the number of worksheets in the workbook.
METHOD 3. Insert an Excel worksheet before the last chart sheet using VBA
VBA
Sub Insert_Worksheet_Before_the_Last_Chart_Sheet()
'insert a new worksheet before the last chart sheet
Worksheets.Add Before:=Charts(Charts.Count)
Worksheets.Add Before:=Charts(Charts.Count)
End Sub
PREREQUISITES
Minimum Number of Chart Sheets: The workbook must have at least one chart sheet. Given a workbook can comprise either or both worksheets and/or chart sheets, and the VBA code is to insert a new worksheet before the last chart sheet there must be at least one chart sheet that the VBA code can refer to and insert a new worksheet before it. If a workbook comprises only worksheets the VBA code will return an error stating "Subscript out of range".
Minimum Number of Chart Sheets: The workbook must have at least one chart sheet. Given a workbook can comprise either or both worksheets and/or chart sheets, and the VBA code is to insert a new worksheet before the last chart sheet there must be at least one chart sheet that the VBA code can refer to and insert a new worksheet before it. If a workbook comprises only worksheets the VBA code will return an error stating "Subscript out of range".
ADDITIONAL NOTES
Notes 1: Charts.Count will count the number of chart sheets in the workbook.
Notes 1: Charts.Count will count the number of chart sheets in the workbook.
WINDOWS SHORTCUT
Shift
F11
NOTES
This shortcut will insert a new worksheet in front of an active sheet. Therefore to insert a worksheet before the last sheet using this shortcut you will need to have the last sheet selected when actioning this shortcut.
This shortcut will insert a new worksheet in front of an active sheet. Therefore to insert a worksheet before the last sheet using this shortcut you will need to have the last sheet selected when actioning this shortcut.
EXPLANATION
This tutorial explains and provides step by step instructions on how to insert a single worksheet before the last sheet using Excel, VBA and Shortcut methods.
This tutorial explains and provides step by step instructions on how to insert a single worksheet before the last sheet using Excel, VBA and Shortcut methods.
Excel Methods: Using Excel you can insert a new worksheet before the last sheet with the New sheet button, a ribbon option or a sheet option.
VBA Methods: Using VBA you can insert a new worksheet before the last sheet, worksheet or chart sheet by referencing to a Sheets, Worksheets or Charts object, respectively. If you intend to insert a worksheet before the last worksheet or a chart sheet you will need to have at least one worksheet or chart sheet in a workbook.
Shortcut Method: Using a Shortcut you can instantly insert a new worksheet before the last sheet by activating (selecting) the last sheet and actioning the shortcut.
ADDITIONAL NOTES
Note 1: Using the sheet or ribbon option, a new worksheet will be inserted in front of an active sheet.
Note 2: Using the New sheet button, a new worksheet will be inserted in after an active sheet.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Insert multiple Excel worksheets | How to insert multiple Excel worksheets at the same time using Excel, VBA and Shortcut methods | |
| Insert an Excel worksheet | How to insert a single Excel worksheet 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 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 before a specific sheet | How to insert a single Excel worksheet before a specific sheet using Excel, VBA and Shortcut methods |