Insert an Excel worksheet as the last sheet
How to insert a single Excel worksheet as the last sheet using Excel and VBA methods.
Select the last sheet > Click New sheet button
| 1. Select the last sheet. 2. Click on the New sheet button. |
 |
Sub Insert_Worksheet_as_Last_Sheet()
'insert a new worksheet as the last sheet
Worksheets.Add After:=Sheets(Sheets.Count)
Worksheets.Add After:=Sheets(Sheets.Count)
End Sub
ADDITIONAL NOTES
Note 1: Sheets.Count will count the number of sheets in the workbook.
Note 1: Sheets.Count will count the number of sheets in the workbook.
METHOD 2. Insert an Excel worksheet as the last worksheet using VBA
VBA
Sub Insert_Worksheet_as_Last_Worksheet()
'insert a new worksheet as the last worksheet
Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets.Add After:=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 as the last worksheet there must be at least one worksheet that the VBA code can refer to and insert a new worksheet after 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 as the last worksheet there must be at least one worksheet that the VBA code can refer to and insert a new worksheet after it. If a workbook comprises only chart sheets the VBA code will return an error stating "Subscript out of range".
ADDITIONAL NOTES
Note 1: Worksheets.Count will count the number of worksheets in the workbook.
Note 1: Worksheets.Count will count the number of worksheets in the workbook.
METHOD 3. Insert an Excel worksheet as the last chart sheet using VBA
VBA
Sub Insert_Worksheet_as_Last_Chart_Sheet()
'insert a new worksheet as the last chart sheet
Worksheets.Add After:=Charts(Charts.Count)
Worksheets.Add After:=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 as the last chart sheet there must be at least one chart sheet that the VBA code can refer to and insert a new worksheet after 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 as the last chart sheet there must be at least one chart sheet that the VBA code can refer to and insert a new worksheet after it. If a workbook comprises only worksheets the VBA code will return an error stating "Subscript out of range".
ADDITIONAL NOTES
Note 1: Charts.Count will count the number of chart sheets in the workbook.
Note 1: Charts.Count will count the number of chart sheets in the workbook.
EXPLANATION
This tutorial explains and provides step by step instructions on how to insert a single worksheet as the last sheet using the Excel and VBA methods.
This tutorial explains and provides step by step instructions on how to insert a single worksheet as the last sheet using the Excel and VBA methods.
Excel Method: This tutorial provides one Excel method that can be applied to insert a worksheet as the first sheet, which uses the New sheet button and is accomplished in two steps.
VBA Methods: Using VBA you can insert a new worksheet as the last sheet, worksheet or chart sheet by referencing to a Sheets, Worksheets or Charts object, respectively. If you intend to insert a worksheet as the last worksheet or a chart sheet you will need to have at least one worksheet or chart sheet, respectively, in a workbook.
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 first sheet | How to insert a single Excel worksheet as the first sheet using Excel, VBA and Shortcut 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 |