How to Create Multiple Sheets in Excel at Once (3 Methods)

Watch Video – Create Multiple Sheets in Excel at Once

Method 1 – Click the “New sheet” Button Multiple Times to Create Multiple Sheets in Excel

Clicking once will create one new worksheet and clicking on the New Sheet button or the plus icon will create one new worksheet each time.

Method 2 – Selecting Multiple Sheets at Once with Ctrl Key and Copying Them

Steps:

Select the worksheets by holding the Ctrl key on the keyboard and click on the name of the sheet one by one to select them.

Right-click on them to open options.
Select the Move or Copy option.

The Move or Copy window will appear.
Check the Create a Copy box.
Press OK.

Copies of the previously selected sheets will be created.

Method 3 – Using Excel VBA Macro to Create Multiple Sheets at Once

Steps:

Go to the top ribbon.
Select the Developer option.
Select the Visual Basic option from the menu.

You can use ALT + F11 to open the Microsoft Visual Basic for Applications window if you don’t have the Developer tab added.

Microsoft Visual Basic for Applications will appear.
from the top menu bar, select Insert.
Select the Module option.

Paste the below VBA code into the box.

Sub CreateMutipleWorksheet()
Dim X_Num As Integer
Dim X_WS_Name As String
On Error Resume Next
xTitleId = "Create Multiple Similar Worksheet"
X_WS_Name = Application.InputBox("Name of Worksheet To Copy", xTitleId, , Type:=2)
X_Num = Application.InputBox("Number of Copy", xTitleId, , Type:=1)
For i = 1 To X_Num
Application.ActiveWorkbook.Sheets(X_WS_Name).Copy _
After:=Application.ActiveWorkbook.Sheets(X_WS_Name)
Next
End Sub

Go to the top menu, press the Run option, and select the Run Sub/UserForm or you can simply press F5 to run the code.

A window will appear named Create Multiple Similar Worksheet.
Insert the name of the worksheet that you want to copy in the box and press OK