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

While working with Microsoft Excel, there is at least one sheet across every spreadsheet. We may build many spreadsheets when dealing with a lot of information in order to arrange the workbooks and make it simpler to discover information. If the data contains the same category it is important to create all the sheets in same format. In this article, we will demonstrate different ways to create multiple sheets with same format in Excel.


Download Practice Workbook

You can download the workbook and practice with them.


4 Effective Ways to Create Multiple Sheets with Same Format in Excel

Once we establish a sheet with a certain format, suppose we wish to make other sheets with the same format. Assume that we have a large dataset with different items which we can not list into one spreadsheet, so we wanted to create multiple sheets with the same format to keep track of the items properly. We have the following dataset. Now, we want to create more datasets with same format.

how to create multiple sheets in excel with same format


1. Copy Excel Worksheet to Create Multiple Sheets with Same Format

Excel enables us to enter information into multiple spreadsheets while having to start over or copy and paste the same information into each one. For instance, we could want the worksheet titles in the spreadsheet to all to have the same content. The text can be typed in one spreadsheet, then copied and pasted into the subsequent spreadsheets as one method of doing this. This might be rather tiresome if you have multiple worksheets.

STEPS:

  • Firstly, format the first sheet and click on the icon in a triangle shape shown in the screenshot below.

how to create multiple sheets in excel with same format

  • Secondly, press Ctrl + C to copy the whole sheet or you can right-click on the mouse and select Copy.

  • After that, go to another sheet and click on the triangle again.
  • Then, press Ctrl + P to paste the sheet with the same format.
  • Alternatively, you can right-click on the mouse and select the first Paste Option to keep the same formatting.

Note: You have to do this for each sheet you want to keep the same format.

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


2. Use Move or Copy Feature to Generate Multiple Sheets with Same Format

A cell’s contents including formulae and the data they produce, cell formats, and comments are all moved or copied by Excel when you move or copy a cell. Let’s look at the procedures to use this feature to create multiple sheets with the same format.

STEPS:

  • To begin with, right-click on the sheet and select Move or Copy.

how to create multiple sheets in excel with same format

  • This will display a Move or Copy dialog box.
  • Further, select the place where you want to move the sheet. Also, checkmark Create a Copy.
  • Then, click OK.

  • This will create a new sheet with the same formatting as the earlier sheet.

Note: Edit the sheet as per the requirements.

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


3. Construct Multiple Sheets with Same Format by Grouping in Excel

When you group a collection of worksheets, any modifications you apply to one spreadsheet are immediately reflected in every spreadsheet in the grouping in the same exact position. Follow the steps down to construct multiple sheets with same formatting by grouping sheets.

STEPS:

  • In the first place, select a sheet then, press Ctrl and again select other sheets. While selecting sheets hold the Ctrl key.
  • This will group all the sheets.

  • Now, you can format any of those sheets and this will create the same format in the other sheets.
  • You can edit those sheets while they need to be edited.
  • As you can see, you format the Group1 sheet. And this will look like this.

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

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

Note: Simple double-clicking on any worksheet in the workbook will ungroup all of its associated worksheets.

Read More: How to Create Multiple Sheets in Excel at Once (3 Quick Ways)


4. Apply Excel VBA to Create Multiple Sheets with Same Format

With Excel VBA, users can easily use the code which acts as excel menus from the ribbon. Let’s follow the procedure to use the VBA code to create multiple sheets in the same format.

STEPS:

  • Firstly, go to the Developer tab from the ribbon.
  • Secondly, click on Visual Basic from the Code category to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.

how to create multiple sheets in excel with same format

  • Instead of doing this, you can just right-click on your worksheet and go to View Code.

how to create multiple sheets in excel with same format

  • This will appear in the Visual Basic Editor where we write our codes to create multiple sheets with same format.
  • Thirdly, click on Module from the Insert drop-down menu bar.

  • This will create a Module in your workbook.
  • And, copy and paste the VBA code shown below.

VBA Code:

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
  • After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

how to create multiple sheets in excel with same format

  • This will open an Input Box.
  • Write the Worksheet Name and press OK.

how to create multiple sheets in excel with same format

  • Another Input Box will appear.
  • Write down the number of worksheets you want to create and press the OK button to continue.

how to create multiple sheets in excel with same format

  • As we want to create 3 worksheets, there are three worksheets with the same name and same format.

VBA Code Explanation

Sub Create_Multiple_Sheets()

Sub is a part of code that is used to handle the work in the code but will not return any value. It is also known as subprocedure. So we name our procedure Create_Multiple_Sheets().

Dim no_of_sheets As Integer
Dim wrksheet_name As String

The DIM statement in VBA refers to “declare,” and it must be used to declare a variable. So, we declare our range to no_of_sheets which is an Integer, and worksheet to wrksheet_name which is a String.

On Error Resume Next
xTitleId = "Create Multiple Sheets"

Here, we titled the Inputbox which will appear after running the code.

wrksheet_name = Application.InputBox("Worksheet Name", xTitleId, , Type:=2)
no_of_sheets = Application.InputBox("How Many Sheet You Want to Create?", xTitleId, , Type:=1)

There is a field where you have to put the name of the worksheet and we name the input box field Worksheet Name. Also, after entering the worksheet name, another field will display where we have to put the number of sheets we want to create.

For i = 1 To no_of_sheets
Application.ActiveWorkbook.Sheets(wrksheet_name).Copy _
After:=Application.ActiveWorkbook.Sheets(wrksheet_name)
Next

This block of code is for copying the number of sheets with the same format and pasting them with the name we just imputed.

End Sub

This will end the procedure.

Read More: How to Create New Sheets for Each Row in Excel (4 Quick Ways)


Things to Keep in Mind

While using Excel VBA code on your worksheet, make sure you save the file with Excel Macro-Enabled Workbook and the extension will be .xlsm.


Conclusion

The above methods will assist you to Create Multiple Sheets with Same Format in Excel. I hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo