Hello Excellers and welcome back to another blog post in my 2019 series of #macromondays. Today let’s write a macro that will allow us to insert or create multiple new worksheets into an Excel workbook using a list of cell values. How handy is that?. Have you ever needed to insert a lot of new worksheets into your Excel workbook, based on a list?. Say, we have multiple salespeople who each need their own worksheet. Well, no more manually inserting the new worksheets and typing the names. So, as a result, this little bit of delightful code does it all for us with one click. Excited?. I am too so, let’s get coding!

In my example, first of all, I am tasked with creating a new Excel workbook. The workbook must contain every salesperson for a specific sales region. This sales region contains 20 sales people which I have the names of in an Excel worksheet already.

So, I need to get Excel to insert 20 sheets into my workbook each named with the names in my salesperson list.

Preparing To Write The Macro

To begin writing our Macro we first will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. 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.

So, as you can see this macro will be useful to reuse in any workbook. Therefore it makes sense that I will create and save this macro for future use in my Personal Macro Workbook.

Learn More About Your Personal Macro Workbook (PMW)

So, if you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro

We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it InsertNamedWorksheets. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]

Sub InsertNamedWorksheets()

End Sub

[/stextbox]

Declaring Variables

We need to declare two variables. This ensures that Excel creates a memory container for these values. In this example, we need to declare

Dim MyCell As Range MyRange As Range

[stextbox id=’info’]

Dim MyCell As Range, MyRange As Range

[/stextbox]

Setting The Range/s

Next we set the ranges to use. In this example our worksheet is called ‘Salespeople’, cell A2, to the end of the range of values.

[stextbox id=’info’]

Set MyRange = Sheets(“Salespeople”).Range(“A2”)
Set MyRange = Range(MyRange, MyRange.End(xlDown))
[/stextbox]

Turning Off Screen & Display Alerts

This part of the code turns off any displays that may occur when the code is being run. It also prevents any flashing screens and can also help speed up the execution of our code.

[stextbox id=’info’]
Application.ScreenUpdating = False
Application.DisplayAlerts = False

[/stextbox]

 

Adding In & Naming New Worksheets

So, this part of the code uses the For Each Loop method to loop all through all of the cells in MyRange. Subsequently, a worksheet is added for each of the cells in our range. The worksheets are then named with the cell values in the range. Excel then loops through all cells, until all of the values are created and named as new worksheets.

[stextbox id=’info’]
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value
Next MyCell
[/stextbox]

 

Turning Back On Screen & Display Alerts

Next, the Screen updates and display alerts are then turned back on at this stage.

[stextbox id=’info’]
Application.ScreenUpdating = True
Application.DisplayAlerts = True

[/stextbox]

 

Ending The Macro

Finally, once all of the worksheets have been inserted and named, the code finally ends with the End Sub piece of code. This was already entered into the module for us when started the type the name of the macro.

[stextbox id=’info’]

End Sub

[/stextbox]

Above all the best part is testing your macro. So, go ahead and see if it works. (It probably will :-)). Share with other Excellers in the comments box below if you have used this macro and how much time it probably has saved you.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

How To Excel At Excel – Macro Mondays Blog Posts.

 

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.