How to Create Multiple Sheets with the Same Format in Excel (4 Methods)

Dataset Overview

We have the following dataset. Now, we want to create more datasets with same format.

Method 1 – Copy Excel Worksheet

• Format the first sheet as desired.
• Click on the triangle-shaped icon (located at the top left corner of the sheet).

• Press Ctrl + C to copy the entire sheet, or right-click and select Copy.

• Go to another sheet, click the triangle again, and press Ctrl + P to paste the sheet with the same format.
• Alternatively, right-click and choose the first Paste option to maintain formatting.

Read More: How to Create Multiple Worksheets from a List of Cell Values

Method 2 – Use Move or Copy Feature

• Right-click on the sheet you want to duplicate.
• Select Move or Copy.

• In the dialog box, choose the destination for the new sheet and check Create a Copy.
• Click OK to create a new sheet with the same formatting.

Read More: How to Create Multiple Sheets in Excel with Different Names

Method 3 – Grouping Sheets

• Select a sheet, then press Ctrl while selecting other sheets (hold down the Ctrl key).
• This groups all the selected sheets.

• Format any of the grouped sheets; changes will apply to all sheets in the group.
• Edit individual sheets as necessary.

• If you go to Group2 sheet this will also be in the same format.

• Similarly, the previous Group3 sheet will also be in the same format.

Read More: How to Create Multiple Sheets in Excel at Once

Method 4 – Excel VBA

• Open your Excel workbook.
• Go to the Developer tab in the ribbon.
• Click on Visual Basic from the Code category to open the Visual Basic Editor. Alternatively, you can press Alt + F11 to open the editor.

• • Alternatively, right-click on your worksheet and select View Code to open the Visual Basic Editor directly.

• In the Visual Basic Editor, click on Insert and choose Module from the drop-down menu.

• This will create a new module in your workbook.
• Copy and paste the following VBA code into the module:

Sub Create_Multiple_Sheets()
Dim no_of_sheets As Integer
Dim wrksheet_name As String
On Error Resume Next
xTitleId = "Create Multiple Sheets"
wrksheet_name = Application.InputBox("Worksheet Name", xTitleId, , Type:=2)
no_of_sheets = Application.InputBox("How Many Sheet You Want to Create?", xTitleId, , Type:=1)
For i = 1 To no_of_sheets
    Application.ActiveWorkbook.Sheets(wrksheet_name).Copy _
    After:=Application.ActiveWorkbook.Sheets(wrksheet_name)
Next
End Sub

• Run the code by clicking the Run Sub button or pressing the keyboard shortcut F5.

• An input box will appear. Enter the desired Worksheet Name and press OK.

• Another input box will appear. Specify the number of sheets you want to create and press OK.

• The specified number of worksheets with the same name and format will be created.

Read More: How to Create New Sheets for Each Row in Excel

Things to Keep in Mind

Remember to save your file as an Excel Macro-Enabled Workbook (.xlsm) when using VBA code.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Understanding Excel Spreadsheets
• How to Insert Sheet from Another File in Excel

<< Go Back to  Insert Sheet | Worksheets | Learn Excel