Creating Worksheets from a List of Names

by Allen Wyatt
(last updated March 2, 2021)


Koen has a worksheet that has a list of names in column A. He needs to create a worksheet for each name in the list and have the worksheet named according to that name. Koen suspects this will require a macro, but he's not sure how to go about such a task.

This task is relatively easy to do if you use a macro, and there are any number of ways you could go about it. One simple way is to select your list of worksheet names and then run the following macro.

Sub AddWorksheetsFromSelection()
    Dim CurSheet As Worksheet
    Dim Source As Range
    Dim c As Range

    Set CurSheet = ActiveSheet
    Set Source = Selection.Cells
    Application.ScreenUpdating = False

    For Each c In Source
        sName = Trim(c.Text)
        If Len(sName) > 0 Then
                Worksheets.Add After:=Worksheets(Worksheets.Count)
                ActiveSheet.Name = sName
        End If
    Next c

    Application.ScreenUpdating = True
End Sub

The macro essentially grabs each cell in your selection, creates a new worksheet, and then renames that worksheet according to whatever was in the cell.

The macro checks to make sure that a particular cell actually contains something (you can't rename a worksheet if there is no name in the cell), but it still isn't nearly as robust as it might be. There could be other flaws in your list of worksheet names that might lead to errors when the macro is run. For instance, what if your list contains duplicates? Or it contains names that Excel doesn't allow? These (and any number of other errors) could be anticipated and the code changed to handle such situations.

While using a macro to create the worksheets is fast and easy, you may want to note that you don't necessarily need to use a macro. In fact, you could use the PivotTable capabilities of Excel to create the desired worksheets. Let's assume, for the sake of this example, that your desired worksheet names are in column A of a worksheet, and that cell A1 contains a heading for the column (such as "Names" or "Worksheets"). What you want to do is to create a PivotTable that is based on these names. Follow these steps:

  1. Select any worksheet name in the column.
  2. Display the Insert tab of the ribbon.
  3. Click the PivotTable tool, at the left side of the ribbon. Excel displays the Create PivotTable dialog box, with your range of worksheet names already specified. (See Figure 1.)
  4. Figure 1. The Create PivotTable dialog box.

  5. Click OK. Excel creates the PivotTable and displays the PivotTable Fields pane at the right side of the screen.
  6. In the PivotTable Fields pane, click the checkbox next to the field used for your list of worksheets. (It should be something like "Names" or "Worksheets.") Excel adjusts the PivotTable.
  7. Drag the checked field name ("Names" or "Worksheets") into the Filters area of the PivotTable Fields pane. (See Figure 2.)
  8. Figure 2. The PivotTable Fields pane with a filter set.

  9. Make sure the Analyze tab of the ribbon is displayed. (It should have been displayed by default after you created the PivotTable.)
  10. Click the down-arrow under the PivotTable tool, at the left side of the ribbon. Excel displays some choices you can make.
  11. Click the down-arrow at the right side of the Options choice. (Don't click the Options choice itself; that displays a dialog box. You want to just click the down-arrow.)
  12. Choose the Show Report Filter Pages option. Excel displays the Show Report Filter Pages dialog box.
  13. Click OK. Excel creates a worksheet for each worksheet name in your list.

It is important to realize that at this point each of the new worksheets contains a small PivotTable. To get rid of these PivotTables, you might think that you can create a selection set of the new worksheets (click the first worksheet tab and then hold down the Shift key as you click the last worksheet tab) and then press the Delete key. In my testing, though, this doesn't work—Excel won't let you make changes to PivotTables in group edit mode. Instead, you'll need to display each worksheet, in turn, and delete the PivotTables.

This may seem like a lot of work, but if you only need to create all these worksheets a single time, it can be a relatively quick way to do it without the need of invoking a macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13463) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Adding Gridlines to a Table

You can easily add borders to your table cells. This tip shows you how.

Discover More

Changing the Color of Worksheet Gridlines

Want the gridlines in your worksheet to be a different color? You aren't limited to stodgy black; Excel lets you make ...

Discover More

Quickly Accessing the Column Tab

If you need to quickly display the Column tab of the Table Properties dialog box, here are some handy tricks you can use. ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Selecting All Visible Worksheets in a Macro

Do you need your macro to select all the visible worksheets (and just the visible ones)? It's not as easy as it sounds, ...

Discover More

Creating a Copy without Formulas

Excel makes copying worksheets (duplicating them) rather easy. However, you may want a worksheet copy that differs from ...

Discover More

Getting the Name of the Worksheet Into a Cell

Excel allows you to change the names assigned to the worksheets in a workbook. If you want to have those names appear in ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 - 3?

2021-12-17 06:28:02


"To get rid of these PivotTables, you might think that you can create a selection set of the new worksheets (click the first worksheet tab and then hold down the Shift key as you click the last worksheet tab)" and then INSTEAD OF pressing the Delete key, this is what worked for me...copy an empty row from another sheet and paste it over the row where the pivot header is

2021-07-18 06:25:37

Jonathan Jones

nice tip, i would add a check that the length of sName is less than 31 as worksheets have a 31 character limit

2021-03-02 11:43:05

Roger Grossi

Good Tip Allen ! This is very helpful - I have nor used Macros in excel and this is great way create these worksheets with selected names etc.

2020-06-02 03:56:09

Alan Elston

...Allen Wyattt also has a similar Tip here:

2020-06-01 10:59:19


My word, how easy that was, thank you! I used the macro - never used a macro before, and was able to find my way around thanks to your clear instructions - oh the joy when I instantly saw 21 sheets with all the correct labels - woohoo!

2019-12-11 15:26:18

Armando Chavez

You made the instructions easy to follow and I thank you for that!

2019-11-11 16:17:04

Ann Marie Reilly

Is there a way to create a link for each of the new tabs so I can email people just their specific tab's info?

2019-11-04 11:50:30


Thank you for this helpful walk through!!!

2019-01-25 05:07:22


Thank you for the pivot table tip. I have found in Excel 2016, I can delete the pivot table in all sheets in one movement, by grouping all sheets , selecting the first two columns, right click and select 'clear contents'. This has worked for my purposes but may have other complications I have not considered .

2018-07-10 06:36:28

Paul McNulty

This is such a great tip - thank you. Have used the pivot table option.
I have a front page tab which contains some basic information for each of the listed contacts and using the tip above have been able to create tabs for all the listed contacts in one foul swoop - absolutely fantastic
Is there anyway of making it so that as I insert a new row or add to the bottom of my list it will automatically add a new tab into the workbook ?

Thanks again for this great tip

2018-06-30 23:34:36


This is so great! Thanks for sharing.

2018-06-30 04:23:19


I shudder to think how many times I've had to do this the hard way ;)
Thank you for sharing your knowledge!

2017-12-04 05:15:50


Super Thank You.

2016-10-31 08:53:50

Daniel H

This is great!

However, is there a way to run this macro and have it make copies of a template sheet and rename them, rather than creating blanks sheets?

I have a template worksheet that updates based on the worksheet name. Eventually, I will need over 100 copies of this sheet, and using the worksheet names they will pull information from a table of data to populate each sheet.

I'd love to use to this macro to create the 100+ copies that I need, but I'm pretty new to macros.

Any ideas are appreciated!

2016-09-12 08:27:52



I'm a she! Lol!

Thanks again!

2016-09-12 08:25:55


Thank you sooooooo much for the post!

It really solved my problem and has made my work darn easier!
I didn't think I'd get a response this quick!

Love the daily tips!
It's one of the best past times for a few minutes before I get off from work!

Thanks again!

2016-09-06 08:55:01


what a great tip it's going to save lots of time

2016-09-04 05:28:37

Chris Plant

You could also adapt the macro to create a hyperlink to individual worksheets from the cell holding their name in the first worksheet.

2016-09-03 19:35:59

Alex B

Hello Mike

Your code module will have a line right at the top with the words "Option Explicit"

sName is not being declared and Option Explicit forces you to do so.

You can either
- remove the Option Explicit statement or
- where all the other Dim statements are insert "Dim sName As String"

If you would rather not have the Option Explicit statement appear in each new module you create then in Developer mode Tools > Options > Editor > Require Variable Declaration - Uncheck this.
(this is in Excel 2010)

2016-09-03 06:34:54

Mike Davies

Great macro and also the pivot table tip but I get a compile error: Variable not defined on

sName = Trim(c.Text)

what should I do?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.