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 the same format.

how to create multiple sheets in excel 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).

how to create multiple sheets in excel with same format

  • 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.

Note: Repeat this process for each sheet you want to keep in the same format.

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


Method 2 – Use the Move or Copy Feature

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

how to create multiple sheets in excel with same format

  • 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.

Note: Edit the new sheet as needed.

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.

Note: Ungroup sheets by double-clicking any worksheet in the workbook.

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.

how to create multiple sheets in excel with same format

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

how to create multiple sheets in excel with same format

  • 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 F5.

how to create multiple sheets in excel with same format

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

how to create multiple sheets in excel with same format

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

how to create multiple sheets in excel with same format

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

VBA Code Explanation

  • Sub Create_Multiple_Sheets(): This subprocedure handles the creation of multiple sheets.
  • Dim no_of_sheets As Integer and Dim wrksheet_name As String: These lines declare variables to store the number of sheets and the desired worksheet name.
  • On Error Resume Next: This line allows the code to continue even if an error occurs.
  • xTitleId = “Create Multiple Sheets”: Sets the title for the input boxes.
  • wrksheet_name = Application.InputBox(“Worksheet Name”, xTitleId, , Type:=2): Asks the user for the worksheet name.
  • no_of_sheets = Application.InputBox(“How Many Sheets You Want to Create?”, xTitleId, , Type:=1): Asks the user for the number of sheets to create.
  • The loop copies the specified sheet and pastes it after the original sheet, creating multiple sheets with the same format.

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


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

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

2 Comments
  1. Reply
    Muhammad Ismail Jumarang Feb 21, 2024 at 10:43 PM

    Thank you so much for your knoladge sharing, my name is Muhammad ismail Jumarang from Indonesia

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo