Have multiple blank worksheets to insert? You can insert multiple worksheets using Excel Macros!

Make sure your Excel has the Developer Tab enabled following this tutorial.

I explain how you can do this below step by step!

What does it do?

Insert multiple worksheets based on the user input of the number of sheets

Copy Source Code:


Sub InsertMultipleWorksheets()

Dim numOfSheets As Integer

'Get the number of sheets from the user
numOfSheets = InputBox("Enter number of sheets to insert", "Enter number of sheets")
'Add the additional sheets after the current active sheet
Sheets.Add After:=ActiveSheet, Count:=numOfSheets

End Sub

Final Result: 

Insert Multiple Worksheets Using Macros In Excel | MyExcelOnline

Exercise Workbook:

Insert Multiple Worksheets Using Macros In Excel | MyExcelOnline

DOWNLOAD EXCEL WORKBOOK


We only have one worksheet so far, let us work out our magic!

Insert Multiple Worksheets Using Macros In Excel | MyExcelOnline

STEP 1: Go to Developer > Code > Visual Basic

Insert Multiple Worksheets Using Macros In Excel | MyExcelOnline

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

Insert Multiple Worksheets Using Macros In Excel | MyExcelOnline

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

Insert Multiple Worksheets Using Macros In Excel | MyExcelOnline

 

Make sure your macro is selected. Click Run.

Insert Multiple Worksheets Using Macros In Excel | MyExcelOnline

Let us try out adding 5 worksheets. Click OK.

Insert Multiple Worksheets Using Macros In Excel | MyExcelOnline

With just one click, you have inserted multiple worksheets!

 Insert Multiple Worksheets Using Macros In Excel | MyExcelOnline

 

How to Insert Multiple Worksheets Using Macros In Excel

 

HELPFUL RESOURCE:

101 Macros Book

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn