Insert multiple Excel chart sheets

How to insert multiple Excel chart sheets at the same time using Excel, VBA and Shortcut methods

METHOD 1. Insert multiple Excel chart sheets using the sheet option

EXCEL

Select multiple sheets > Right-click on a sheet > Insert > Chart > OK

1. Press and hold the Shift key and select the number of sheets that you want to insert.
Note: in this example we are inserting three new chart sheets and therefore have selected three sheets.

2. Right-click on any of the selected sheets.
3. Click Insert.
Note: the new chart sheets will be inserted in front of the sheet that you have right-clicked on. In this example the three new chart sheets will be inserted in front of Sheet3, given we have right-clicked on Sheet3..

Right-click on one of the selected sheets and select Insert - Excel

4. Select Chart and click OK.

METHOD 1. Insert multiple Excel chart sheets using VBA

VBA

Sub Insert_Multiple_Chart_Sheets()

'insert three new chart sheets in front of an active sheet
Charts.Add , Count:=3
'the 3 in Count:=3 represents the number of new chart sheets that will be inserted
'replace the Count number with the number of new chart sheets you want to insert

End Sub

OBJECTS
Charts: The Charts object is a collection of all chart sheets.

ADJUSTABLE PARAMETERS
Number of New Chart Sheets to Insert: Select the number of new chart sheets you want to insert by replacing the Count number. In this example we are inserting three new chart sheets and have directly entered into the VBA code the number of new chart sheet to be inserted.
Sheet Selection: Select the sheet in front of which you want to insert the new chart sheets.

METHOD 2. Insert multiple Excel chart sheets using VBA by referencing to a cell

VBA

Sub Insert_Multiple_Chart_Sheets()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Parameters")

'insert new chart sheets in front of an active sheet using a cell reference that captures a value that represents the number of new chart sheets to be inserted
Charts.Add , Count:=ws.Range("A1").Value
'this example assumes that cell ("A1") in the Parameters worksheet holds a value that represents the number of new chart sheets to be inserted.

End Sub

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

PREREQUISITES
Worksheet Name: Have a worksheet named Parameters.
Number of New Chart Sheets to Insert: Have the value that represents the number of new chart sheets 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 chart sheets to insert.

ADJUSTABLE PARAMETERS
Number of New Chart Sheets to Insert: Select the number of new chart sheets 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.
Sheet Selection: Select the sheet in front of which you want to insert the new chart sheets.

Insert multiple chart sheets using a Shortcut

SHORTCUT

WINDOWS SHORTCUT

F11

NOTES
To insert multiple chart sheets using this shortcut you will need to select the number of new chart sheets you want to insert and then action the shortcut. In this example we are inserting three new chart sheets, therefore, we need to select three sheets, as per the image in Step 1 of Method 1, and then action the shortcut.

Explanation about how to insert multiple chart sheets

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to insert multiple chart sheets using the Excel, VBA and Shortcut methods.

Excel Method: Using Excel you can insert multiple chart sheets by using a sheet option.

VBA Method: Using VBA you can insert multiple chart sheets by referencing to a Charts object. You can also select the number of new chart sheets to insert by directly entering the number into the VBA code or include reference to a cell that captures the value that represents the number of new chart sheets to insert. By referencing to a cell that captures the value that represents the number of new chart sheets to insert is a more dynamic approach. This will allow you to change the number in a single cell that represents the number of new chart sheets to insert, before running the VBA code. The method that has the number directly entered into the VBA code would require you to change the number in the VBA code every time you want change the number of new chart sheets to insert.

Shortcut Method: Using a Shortcut you can insert multiple chart sheets in front of an active sheet by selecting the number of chart sheets you want to insert and actioning the shortcut.

ADDITIONAL NOTES
Note 1: When the VBA code excludes reference to a specific sheet before or after which to insert new chart sheets, new chart sheets will be inserted in front of an active sheet.