Works for me but I used a limited range of A1:A20 when I tested.
Make sure you have enough names in Master sheet range A1:A100 or you will throw
an error.
None of the names can be duplicates.
Test it yourself with a,b,c,d,e,f,g,h,i in A1:A9
I would go with Dave Peterson's code istead of the code you have.
Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly
Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range
Set TemplateWks = Worksheets("Master")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell
End Sub
Gord
On Wed, 1 Aug 2007 11:06:02 -0700, e12762r
wrote:
Forget about that now; I'm getting fancier. I saw that you gave someone code
to Create Tabs based on a list; and simultaneously have those new Worksheet
be a copy of the Original "Master". This is that Code: But it hasn't worked
for me! Can you test it and help me figure out what I'm doing wrong?
Assuming Master sheet is named "Master" and has a list in A1:A100
Sub Copy_Sheet()
Dim rCell As Range
For Each rCell In Sheets("Master").Range("A1:A100")
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = rCell.Value
ActiveSheet.Range("B2").Value = rCell.Value
Next rCell
End Sub
Each new sheet will be a copy of "Master" with a new name and have that sheet
name in B2
"Gord Dibben" wrote:
What is "doesn't work"?
It is meant to run automatically on a calculated change in C2 value.
Did you place the code into the sheet module?
Did you check out the google search thread?
The code you have chosen is a regular macro and requires you to to run manually.
Gord
On Tue, 31 Jul 2007 12:26:09 -0700, e12762r
wrote:
Gord,
Thank You! Actually this exact one didn't work; but in searching through
these forums - I found & used this:
Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = ws.Range("B4").Value
Next ws
End Sub
But, in doing so, recognize and salute you as a true 'Excel MVP'
"Gord Dibben" wrote:
Yes
Basic event code in the worksheet module would be.................
Private Sub Worksheet_Calculate()
Me.Name = Range("C2").Value
End Sub
But I would suggest you check out this google search thread for various sets of
event code from Dave Peterson that cover several scenarios.
http://groups.google.com/group/micro...c8d6cf55155e92
Gord Dibben MS Excel MVP
On Tue, 31 Jul 2007 09:56:41 -0700, e12762r
wrote:
Does anyone know if it's possible to Name an Excel Worksheet Tab based on
some kind of reference (cell) or calculation or function?