Dataset Overview
We have the following dataset. Now, we want to create more datasets with the 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 the 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 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.
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!
Thank you so much for your knoladge sharing, my name is Muhammad ismail Jumarang from Indonesia
Hello Muhammad Ismail Jumarang,
You are most welcome.
Regards
ExcelDemy