Insert an Excel chart sheet before the last sheet

How to insert a single Excel chart sheet before the last sheet using Excel, VBA and Shortcut methods

METHOD 1. Insert an Excel chart sheet before the last sheet using the sheet option

EXCEL

Right-click on the last sheet > Insert > Chart > OK

1. Right-click on the last sheet.
2. Click Insert.

Right-click on the last sheet and select Insert - Excel

3. Select Chart and click OK.

METHOD 1. Insert an Excel chart sheet before the last sheet using VBA

VBA

Sub Insert_Chart_Sheet_Before_the_Last_Sheet()

'insert a new chart sheet before the last sheet
Charts.Add Before:=Sheets(Sheets.Count)
'Sheets.Count will count the number of sheets in the workbook

End Sub

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

METHOD 2. Insert an Excel chart sheet before the last worksheet using VBA

VBA

Sub Insert_Chart_Sheet_Before_the_Last_Worksheet()

'insert a new chart sheet before the last worksheet
Charts.Add Before:=Worksheets(Worksheets.Count)
'Worksheets.Count will count the number of worksheets in the workbook

End Sub

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

PREREQUISITES
Minimum Number of Worksheets: The workbook has 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 chart sheet before the last worksheet there must be at least one worksheet that the VBA code can refer to and insert a new chart sheet before it. If a workbook comprises only chart sheets the VBA code will return an error stating "Subscript out of range".

METHOD 3. Insert an Excel chart sheet before the last chart sheet using VBA

VBA

Sub Insert_Chart_Sheet_Before_the_Last_Chart_Sheet()

'insert a new chart sheet before the last chart sheet
Charts.Add Before:=Charts(Charts.Count)
'Charts.Count will count the number of chart sheets in the workbook

End Sub

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

PREREQUISITES
Minimum Number of Chart Sheets: The workbook has 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 chart sheet before the last chart sheet there must be at least one chart sheet that the VBA code can refer to and insert a new chart sheet before it. If a workbook comprises only worksheets the VBA code will return an error stating "Subscript out of range".

Insert a chart shet before the last sheet using a Shortcut

SHORTCUT

WINDOWS SHORTCUT

F11

NOTES
To insert a chart before the last sheet using this shortcut you will need to have the last sheet selected when actioning this shortcut, given the shortcut inserts a new chart sheet in front of an active sheet.

Explanation about how to insert a chart sheet before the last sheet

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to insert a single chart sheet before the last sheet using Excel, VBA and Shortcut methods.

Excel Methods: Using Excel you can insert a new chart sheet before the last sheet with a sheet option.

VBA Methods: Using VBA you can insert a new chart sheet before the last sheet, worksheet or chart sheet by referencing to a Sheets, Worksheets or Charts object, respectively. If you intend to insert a chart sheet before the last worksheet or a chart sheet you will need to have at least one worksheet or chart sheet, respectively, in a workbook.

Shortcut Method: Using a Shortcut you can instantly insert a new chart sheet before the last sheet by having the last sheet selected when actioning the shortcut.

ADDITIONAL NOTES
Note 1: Using the sheet option, a new chart sheet will be inserted in front of an active sheet.