How to quickly create multiple sheets with same format in Excel?

If you've created a sheet in Excel with a specific format and need to replicate that format across multiple sheets, you may be wondering how to do it quickly. This tutorial will guide you through several methods to efficiently create multiple sheets with the same formatting.

Create worksheets with same format by Move or Copy

Create worksheets with same format by VBA

Create worksheets with same format by using Kutools for Excel

Create worksheets with same format by Move or Copy

In Excel, you can use the Move or Copy command to create a sheet with same format as the sheet you select.

1. Click at the sheet which is with the format you need on the "Sheet Tab bar", and right click to select "Move or Copy" from the context menu. See screenshot:

2. Then in the popping dialog, check "Create a copy" option. See screenshot:

3. Click "OK". A new sheet is created with same format in the front of the selected sheet. See screenshot:

Note:

(1) You also can click "Home" > "Format" > "Move or Copy Sheet" to enable the" Move or Copy" dialog.

(2) With this method will be time-consuming when you need to create hundreds of sheets with same format.

Create worksheets with same format by VBA

There is a VBA code can help you quickly create multiple worksheets with same format at once.

1. Press "Alt" + "F11" keys to open "Microsoft Visual Basic for Applications" window, then click "Insert" > "Module", copy and paste below code to the "Module" window.

VBA: Create worksheets with same format.

Sub CopyWorkSheets()
'Updateby20150526
Dim xNumber As Integer
Dim xWsName As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
xWsName = Application.InputBox("Copy worksheet name", xTitleId, , Type:=2)
xNumber = Application.InputBox("Copy number", xTitleId, , Type:=1)
For i = 1 To xNumber
    Application.ActiveWorkbook.Sheets(xWsName).Copy _
    After:=Application.ActiveWorkbook.Sheets(xWsName)
Next
End Sub

2. Press "F5" key to run the VBA code, then a dialog pops out for you to type the worksheet name you need to copy its format.

3. Click "OK", and specify the number of copies you need into another popping dialog. See screenshot:

4. Click "OK". Then there are six copies of worksheets with the same format created.

Create worksheets with same format by using Kutools for Excel

If you do not like to use VBA, here I introduce you a handy tool – "Kutools for Excel", you can use its "Copy Worksheets" utility to quickly and easily create sheets with same format.

1. Click "Kutools Plus" > "Worksheet" > "Copy Worksheets". See screenshot:

2. In the "Copy Multiple Worksheets" dialog, do as below:

(1) Check the worksheet name you want to copy its format from the list box of "Copy the selected worksheets";

(2) Specify the number of the copies;

(3) Specify the place you want to put the copies.

3. Click "Ok", a dialog pops out to remind you the copies are successfully created. Click "OK" to close it.

Now you can see the specified number of worksheets have been created with same format.

Click here to know more details about Copy Multiple Worksheets.