Auto Generate and name Worksheets from a list [Solved/Closed]

E-Coli - Sep 2, 2008 at 02:33 PM - Latest reply:  Gagan
- Apr 3, 2017 at 10:47 AM
Hello,

How can I automatically create and name worksheets in a workbook based on a list that exists in another sheet "Summary" in the workbook? The list begins at cell A10. Mind you, this list will vary from workbook to workbook. Do you have some code already compiled to perform this?

E-Coli
See more 

15 replies

Best answer
Ivan-hoe 436 Posts Saturday February 16, 2008Registration date October 17, 2008 Last seen - Sep 6, 2008 at 01:26 AM
122
Thank you
Hello E-Coli,
apparently my tips did not help you.
anyway, here is a solution :
Sub CreateSheetsFromAList()
    Dim MyCell As Range, MyRange As Range
    
    Set MyRange = Sheets("Summary").Range("A10")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Next MyCell
End Sub

I.

Thank you, Ivan-hoe 122

Something to say? Add comment

CCM has helped 1665 users this month

agost003 1 Posts Wednesday April 16, 2014Registration date April 16, 2014 Last seen - Apr 16, 2014 at 03:04 PM
Hi Ivan-hoe / E-Coli -
I found you solution perfect for my needs. I have one question tho. How would you handle the new sheet value if your range was actually a list of dates. In my case a1 : aNN is numeric 1 - NN cell b1 : bNN are dates. I can use the cells in 'a' as my range. But if I use those in 'b', I get an error. I could use 'a' in the range, but would want the corresponding values in b concatenated as part of the new ws name. For insance. A contains 1 : 52 and B is a date seven days apart. I'd like each new sheet to be 'Pay Period for <datevalue> (datevalue derived frm the b1 : b52

TIA,
Jeff A
Thanks Ivan-hoe, it works perfectly.
SuspectHoon 1 Posts Tuesday October 14, 2014Registration date October 14, 2014 Last seen - Oct 14, 2014 at 07:22 AM
Thanks Ivan-hoe!
Is there a way that after it names the Sheet it can add Data from the List to the New Worksheet?

I don't know the code (Thats why I'm asking, haha) but something around the below?


For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
???Im thinking something here???
End Sub

That way the Macro would be able to name the Sheet and also update other information from the List.
How do you use it ... what do you name what ... how do you get this to work in real life
Mate you're a genius, saved me so much time. Thanks a bunch
10
Thank you
Hi

It was really useful your formula but I would need the others worksheets to be equal to the first one. I've trying to do it but I wasn't able to do it. Can you help me one that?
Sub AutoAddSheet()

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Summary").Range("A10")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets(9).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet as a copy of Sheet number 9 in this example
Sheets(Sheets.Count).Name = MyCell.Value 'Renames the new worksheets
Next MyCell
End Sub
Hi subop and Ivan, Excellent solutions! How do I create a worksheet containing the names of the worksheets in the book? Against each worksheet name, I would like to display the content of cell K2 of each of those worksheets.

TIA
Ramakrishna
Helo,
I was wondering if it is possible to add a "hyperconnection" (klickable link) between the name in the list and the worksheet whit the same name?

So that when you crate a new worksheet it also adds a connection between the list and the new worksheet. Easyer to navigate between the worksheets, if the list is long, this way.

Hope to get som e help on this topic.

RT
Good one. I tried this and since my summary tab had 300 records, there were 300 sheets created. Now how do I remove those sheets and restrict it to only 10. I dont want to create a new excel sheet as sooner or later I would need the command for removing the sheet automatically like creating.