Hello Excellers, welcome back to another #MacroMonday blog post in my #Excel 2021 series. Today I will share a quick and simple Excel Macro. It allows a user to insert as many new worksheets into their Excel workbook as they need to. An Input Box is used to collect the number of worksheets needed. The code is available at the end of the blog post.
So, sit back and enjoy inserting multiple new worksheets at the click of a mouse. Yes, that is correct, no more right click and insert multiple times.
On the first Wednesday of EVERY month, I share three FREE Excel tips. Sign up to receive these tips direct to your mailbox. Do join thousands of other subscribers. In short, we like Excel. Do you?.
Prepare To Write The Macro
First, open the Visual Basic Editor. There are two ways to do this. Hit ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
If you cannot see the Developer Tab you may need to enable it. Check out my very short YouTube video for instructions. I will show you how to enable the developer tab in just a few minutes.
You then have a choice. You can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?.
If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
Learn More About Your Personal Macro Workbook (PMW)
Therefore, if you want to read more about your Excel PMW then check out my blog posts below.
Creating and Updating Your Personal Macro Workbook
Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
To start with, I want to reuse this handy macro over and over again. So, I will insert a new module into my Personal Macro Workbook. It is usable over and over again .
Starting The Macro.
After the new module is inserted, type Sub then the name of the Excel macro. In this example, I have named my Macro InsertNewWorksheets.
[stextbox id=’info’]
Sub InsertNewWorksheets()
End Sub
[/stextbox]
Declaring Variables.
Next, any variables need to be declared. This simply means that a memory container is created in Excel to store these values. Only one variable needs to be declared in this macro. It will be the number representing how many worksheets the user wants to insert into the workbook. This will always be a whole number. So, an integer is an exact type we need.
[stextbox id=’info’]
Dim SheetNum As Integer
[/stextbox]
Use An Input Box To Collect Data.
The next stage is to collect the number of worksheets that need to be inserted into the workbook. Using an input box is a quick and easy way to do this. The number of worksheets to be inserted is the user-provided number.
[stextbox id=’info’]
SheetNum = InputBox(“How Many Sheets Do you Want To Insert?”)
[/stextbox]
So, there are a number of options available with the Input Box. I have kept it simple in this example. I only use the first argument. (Prompt). This asks the question to the user how many worksheets to insert. This is enough information for this simple VBA macro to be effective.
Insert The Worksheets.
Subsequently, the number of worksheets entered by the user is passed to the next part of the code. This number is used to create new worksheets. Afterwards, new worksheets are entered after the active worksheet.
[stextbox id=’info’]
Sheets.Add After:=ActiveSheet, Count:=SheetNum
[/stextbox]
This works REALLY fast. I hope you give this a go!.
Ending The Macro.
The code ends once all worksheets are inserted. The End Sub has already been entered by Excel once the Sub code is entered.
[stextbox id=’info’]
End Sub
[/stextbox]
Copy The Excel VBA Macro Code.
Last but not least, the full code is displayed below. Please feel free to copy and use it. Finally, it is easy to insert multiple Excel worksheets at once!.
[stextbox id=’info’]
Sub InsertNewWorksheets()
‘macro by www.howtoexcelatexcel.com
Dim SheetNum As Integer
SheetNum = InputBox(“How Many Sheets Do you Want To Insert?”)
Sheets.Add After:=ActiveSheet, Count:=SheetNum
End Sub
[/stextbox]
As a Final Point. Disclaimer. Always make a backup of your Excel workbook before running any code.
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. Finally, you will receive my free Ebook, 30 Excel Tips. All of my Excel blog posts are collated below.
How To Excel At Excel – Macro Mondays Blog Posts.
I have teamed up with Excel Rescue. If you need help with Excel fast, click the link below. In fact, no Excel problem is too big or too small. Have you used Excel Rescue?. Share your experience in the comments section below.
Also, post your Excel questions in the comments section below. What Excel questions do you have?.