Creating Excel worksheets automatically

15 Responses to Creating Excel worksheets automatically

1. Gary H. says:

   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.
2. NC says:

   January 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…
3. Steve says:

   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?
4. Drew says:

   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
5. NC says:

   September 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
6. David says:

   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
7. Lynn says:

   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!
8. NC says:

   June 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).
9. David says:

   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,
10. NC says:

    March 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.
11. Chuck says:

    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.
12. NC says:

    May 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…
13. Shawn says:

    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.
14. alva says:

    June 7, 2012 at 9:13 am

    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 says:

    December 9, 2012 at 12:25 am

    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!