Auto Generate and name Worksheets from a list

Solved/Closed
E-Coli - Sep 2, 2008 at 02:33 PM
 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

2 replies

Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
Sep 6, 2008 at 01:26 AM
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.
123
please let me know where exactly script shouldbe done becus I am not familiar with macros but I would like to create sheet name for about a year datewise from the month of NOV 2009 in a single work book or atleast for a period of 3 months

pls reply immdtly

thanks
0
This was beautiful - Than you E-Coli and Ivan-hoe!
0
mfcowell3619
Posts
1
Registration date
Wednesday October 17, 2012
Status
Member
Last seen
October 17, 2012

Oct 17, 2012 at 09:10 AM
I see this is old but need help on a similar topic. All of my formulas for my TOTALS page point to Sheet1, Sheet2, etc. Now that I have this macro which worked beautifully my formulas are jacked up. How do I get them to automatically update with the newly assigned sheet names? Also, is there a way to add these sheet names to existing sheets with data and formulas already preset???
0
Hi Ivan,

Your solution is fantastic! May I ask is it possible to create the new worksheet based on a template worksheet in the workbook?

Many thanks!
0
agost003
Posts
1
Registration date
Wednesday April 16, 2014
Status
Member
Last seen
April 16, 2014

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
0
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?
10
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
0
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
0
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
0
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.
0