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.

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

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.

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 have the same content. The text can be typed into one spreadsheet, and 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.

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

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.

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

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

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

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.

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

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

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

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

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

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

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

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.

Download Practice Workbook

You can download the workbook and practice with them.

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.

Related Articles

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

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