Creating Worksheets from a List of Names

Written by Allen Wyatt (last updated September 24, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

Koen has a worksheet that has a list of names in column A. She 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 she'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

    CurSheet.Activate
    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, Excel in Microsoft 365, and 2021.

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. ...

MORE FROM ALLEN

Determining the Hour of the Day

Need to figure out the hour represented by a particular time value? It's easy to do in a macro; just use the Hour ...

Discover More

Indirect References to a DSUM Parameter

Indirect references can be very helpful in formulas, but getting your head around how they work can sometimes be ...

Discover More

Listing Dates at Regular Intervals

Need a way to enter dates for every other Tuesday (or some other regular interval)? Excel makes it easy, providing ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Using Very Long Worksheet Tab Names

Excel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides ...

Discover More

Limiting How Many Times a Worksheet Can Be Calculated

Excel, by default, recalculates your worksheets as you make changes in those worksheets. If you want to limit the number ...

Discover More

Referencing Worksheet Tabs

Ever want to use the name of a worksheet tab within a cell? Here's how you can access that information using the CELL ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 five more than 4?

2022-12-02 23:25:19

Tomek

Pam
I tried very hard to replicate your problem including using foreign characters in the list of names and other non-alphabetic characters. Every time I got the sheets created and renamed, sometimes the weird characters I used were replaced by different ones.

The only situation when the macro failed for me was when I had very long names (Sheet names can only be 31 characters or less) or when the name contained some forbidden characters: :\/?*[]
Even in these cases the macro did not completely run but displayed an error 1004 message, which you did not mention seeing.

The only other thing that comes to my mind is that may be your inserted sheets are protected from renaming. Can you rename them manually?
Why this may happen is a different story, so unless you find protection is the case I am not going to speculate here.

If you need more help, post another comment. You can also send me an e-mail directly by clicking on my name on the left; I un-hid my e-mail for this comment. I will try to reply promptly, but I may be away for a day or two.


2022-12-01 16:34:12

Pam

Its creating the individual worksheets but not naming them from the names in my list. They are just named Sheet2, Sheet3, Sheet4, and so on. What did I do wrong


2022-09-25 09:51:50

Alex B

The pivot table method was interesting. I didn't have any trouble selecting all the created sheets and deleting the pivot tables in one go, as long as I either selected the whole pivot table OR the whole sheet.
(Running MS 365)


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
Subscribe

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.