Creating Excel worksheets automatically

A question from Askville:

How can I insert worksheets in excel and name them automatically from a list?

In an excel workbook, I have a list of 36 words and I want a separate worksheet for each of them – all named.

Write a macro. Something along the lines of:

Sub CreateAndNameWorksheets()
    Words = Cells.Range("A1:A36")
    For Each Item In Words
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = Item
    Next Item
End Sub

The code above assumes that your list of words is stored in cells A1:A36 of the worksheet named Words.

15 thoughts on “Creating Excel worksheets automatically

  1. Gary H.

    I can’t get your Sub to run. Gets a “sub/function not defined.” I played with it and got past that, but now I get Syntax errors for the lines “For Each Item In Words” and “Sheets.Add After:=Sheets(Sheets.Count).

    I don’t really have a grasp of VB, so the syntax stumps me. I am testing this in Excel 2003, but was hoping to use it in a Excel07 file.

    Thanks for any pointers.

  2. NC Post author

    The notes to the code say plainly, “The code above assumes that your list of words is stored in cells A1:A36 of the worksheet named Words.” If you don’t have such a worksheet in your workbook, the Words variable never gets populated, so it doesn’t have a Range or Array type, so an attempt to use it as an argument in a “For Each” construct throws an error, as For Each expects an enumeration of some kind, over which it could iterate.

    I deliberately didn’t write any error-checking, since the idea was to show how to do stuff with available data. Putting error-checking in place would have obscured the functionality…

  3. Steve

    The code works great for me, thanks a million! I only have one thing next to figure out. When I load the code, it works fine as long as I have a value in the range of cells. My range will never exceed a1:36, but will not always be up to 36. One day may be 24 and the next 34. I dont want to write a new code each time. Can I enter the code somehow, that will only creat a worksheet if the cells in that range have a value of <0?

  4. Drew


    The above code works just how I need thanks, however I want to slightly mod it so that instead of it adding a new set of worksheets from the list, I want to to copy a master worksheet and rename it using the list.

    I am not even anywhere near being good with VB so any help would be great.

    I have tried changing it to use Sheets.Copy but it just copies every worksheet over and over and also new ones using the list.


  5. NC Post author


    Instead of Sheets.Copy, you should try Sheets(“SheetName”).Copy. Something along these lines:

    Sub CopyMasterSheet()
      Words = Cells.Range("A1:A4")
      For Each Item In Words
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = Item
      Next Item
    End Sub

  6. David

    Hi.. I should prob be able to figure this out myself but clearly I am no good w/ VB. I’m looking to mod the code to name each sheet (25 total) based on the text in cell C4 of each sheet. How would I modify? And where would I place the code–in each worksheet or in the workbook?

    Using Excel 03.

    Thanks in advance

  7. Lynn

    OMG. You are a genius. I am a complete novice in using html (or whatever this code is?!?) and this worked for me! Thank you for saving me several hours of work!

  8. NC Post author


    I am no genius, but I am happy that you found it useful. And, just so you know, it’s called VBA (Visual Basic for Applications).

  9. David

    NC I get Run-time error ‘1004’:
    You typed an invalid name for a sheet or chart. Make sure that:
    >The name that you type does not exceed 31 characters.
    >The name does not contain any of the following characters: :\/?*[ or ]
    >You did not leave the name blank.

    The issue is with Sheets(Sheets.Count).Name = Item

    Sub DailyReportPopulator()
    Executive_Summary = Cells.Range(“B4:B59”)
    For Each Item In Executive_Summary
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = Item
    Next Item
    End Sub

    Any help would be greatly appriciated.


  10. NC Post author


    Check the data in your Executive_Summary!B4:B59. Looks like some of the values contained in the cells of this range can’t be used as worksheet names, because they are either too long, or empty, or contain characters not allowed in worksheet names.

  11. Chuck

    I am looking to do something very similar. Only I need the first sheet of the workbook to be a log of all the sheets following it. Which all have the same form that will be populated with different information but not the same form as the log sheet.
    If all of the sheet names were created from a new entry in a list in the log sheet like (a1:a2000). That would work. But it would be nice if it worked both ways. Where if I picked on the new sheet tab it would make the new sheet from the form and auto name it based on the last sheet name +1 as the sheet names will all be a 5 digit numeric value in sequence from 00001 to 99999 and create the new row with this sheet name in A column in the log file. Also I need the log to access the information in certain cells of the new sheet that is associated with the sheet name in the A column of the log sheet and displayed in the row associated with the sheets name in the log sheet. I am fully aware that beggars can’t be chooser’s and since I don’t grasp VB code at all. I will be ecstatic with anything that will accomplish this. either way.

  12. NC Post author


    I am looking to do something very similar. Only I need the first sheet of the workbook to be a log of all the sheets following it.

    Off the top of my head, something like this should work:

    Sub ListSheets()
        Num = 1
        For Each Item In Sheets
            CellRef = "A" & Num
            Sheets("TOC").Range(CellRef) = Item.Name
            Num = Num + 1
        Next Item
    End Sub

    This assumes that the first sheet is called TOC.

    Automatic naming is also doable, but you’ll need to put the code into ThisWorkbook object, rather than into a module were it would go by default:

    Private Sub Workbook_NewSheet(ByVal Sh As Object)
        If TypeName(Sh) = "Worksheet" Then
            Count = "0" & Sheets.Count + 1
            While Len(Count) < 5
                Count = "0" & Count
            Sh.Name = Count
        End If
    End Sub

    As to the whole logging thing, I am simply not sure I understand your requirements…

  13. Shawn

    This is what I have, but I keep getting the error, “Compile error: Expected End Sub”

    Sub AutoPop()

    ‘ AutoPop Macro

    ‘ Keyboard Shortcut: Ctrl+Shift+P

    Sub CreateAndNameWorksheets()
    Words = Cells.Range(“A2:A9”)
    For Each Item In Test1
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = Item
    Next Item
    End Sub

    Any help would be much appreciated.

  14. alva

    I want to hve a work sheet created automatically when ever i feed the data in the first col of the first sheet named master sheet

  15. Sam

    Thanks a heap! I have tried others but they reversed the sheets (ie the one I wanted first was last and they entered after my first sheet) Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *