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.
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.
- 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.
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.
- 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.
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.
- 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.
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.
- 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.
- And, copy and paste the VBA code shown below.
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.
VBA Code Explanation
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.
This will end the procedure.
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.
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!