If you are searching for the solution or some special tricks to create multiple sheets in Excel at once then you have landed in the right place. Sometimes, you may need to create multiple sheets with same format in Excel, sometimes, you want create multiple new worksheets in Excel. There are some easy ways to create multiple sheets in Excel at once. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.
In this section, I will show you 3 quick and easy methods to create multiple sheets in Excel at once on Windows operating system. You will find detailed explanations of methods and formulas here. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If any methods don’t work in your version then leave us a comment.
1. Click the “New sheet” Button Multiple Times to Create Multiple Sheets in Excel
You can simply create multiple worksheets by clicking the Plus icon beside the name of the sheets. After clicking once it will create one new worksheet and clicking each time on the New Sheet button or the plus icon, it will create one new worksheet.
Read More: Understanding Excel Spreadsheets
2. Selecting Multiple Sheets at Once with Ctrl Key and Copying Them
You may need to create a copy of multiple sheets at once. For this, Excel has a built-in feature. Follow the steps below:
- First, you have to select the worksheets. For this, hold the Ctrl key on the keyboard and click on the name of the sheet one by one to select
- When the selection of sheets is done, then right-click on them to open options.
- From the options, select the Move or Copy
- Then, the Move or Copy window will appear.
- Check Create a Copy box.
- Next, press OK.
- As a result, you will see there will create new sheets which are the copy of the previously selected sheets.
3. Using Excel VBA Macro to Create Multiple Sheets at Once
If you want to create multiple copy sheets from one sheet at once then you can use a VBA macro code. Here, I will share with you the code and show you how you can apply it to a workbook.
- For this, first, go to the top ribbon press on the Developer option, and then press on the Visual Basic option from the menu.
You can use ALT + F11 to open the Microsoft Visual Basic for Applications window if you don’t have the Developer tab added.
- Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “Module’” option.
- Now, a new “Module” window will appear. And Paste this VBA code into the box.
Sub CreateMutipleWorksheet() Dim X_Num As Integer Dim X_WS_Name As String On Error Resume Next xTitleId = "Create Multiple Similar Worksheet" X_WS_Name = Application.InputBox("Name of Worksheet To Copy", xTitleId, , Type:=2) X_Num = Application.InputBox("Number of Copy", xTitleId, , Type:=1) For i = 1 To X_Num Application.ActiveWorkbook.Sheets(X_WS_Name).Copy _ After:=Application.ActiveWorkbook.Sheets(X_WS_Name) Next End Sub
- To run the code go to the top menu, press on the Run option, and here will open some other options and select the Run Sub/UserForm also you can simply press F5 to run the code.
- When you run the code, a window will appear named “Create Multiple Similar Worksheet”.
- Then, insert the name of the worksheet that you want to copy in the box and press OK
- After that, one more window will appear and will ask you to enter the number of copies that you want to create for the selected worksheet.
- After inserting the number press OK.
- As a result, you will see there will create 3 copies of the selected worksheet.
You can create your own file in XLSM format and apply the methods described here, or download the following Excel file and practice the methods.
In this article, you have found how to create multiple sheets in Excel at once. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.