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()
    Sheets("Words").Select
    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”

Gary H. January 7, 2009 at 9:31 pm

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.

NC Post authorJanuary 8, 2009 at 5:59 am

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…

Steve February 20, 2009 at 1:43 pm

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?

Drew September 8, 2009 at 1:39 am

Hello,

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.

Thanks

NC Post authorSeptember 10, 2009 at 1:51 pm

Drew,

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

Sub CopyMasterSheet() Sheets("Master").Select 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

David December 1, 2009 at 11:35 am

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
Dave

Lynn June 7, 2011 at 8:11 am

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!

NC Post authorJune 8, 2011 at 1:22 pm

Lynn,

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

David March 8, 2012 at 8:15 am

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()
Sheets(“Executive_Summary”).Select
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.

Thanks,

NC Post authorMarch 8, 2012 at 12:20 pm

David,

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.

Chuck May 15, 2012 at 12:01 pm

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.

NC Post authorMay 16, 2012 at 12:17 am

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.

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
        Wend
        Sh.Name = Count
    End If
End Sub

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

Shawn June 6, 2012 at 8:36 am

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()
Sheets(“Test1”).Select
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.