How could I use a named range to create a series of new worksheets?

Details:
I have a named range (A1:A3) called NewSheetNames. I want to create a
new worksheet for each value in the named range. (3 sheets in this
example).

Example:

NewSheetNames (named range)
A1: Sheet1
A2: AnotherSheet
A3: NewSheet

Click button, and vba creates three new sheets called:
1. Sheet1
2. AnotherSheet
3. NewSheet

Any pointers are greatly appreciated!

On Jan 10, 11:18*am, John wrote:
How could I use a named range to create a series of new worksheets?

Details:
I have a named range (A1:A3) called NewSheetNames. I want to create a
new worksheet for each value in the named range. (3 sheets in this
example).

I found some code that got me started. Think this should work, but if
there's a better way I'm all ears!



Sub Create_Worksheets()

Dim rngMemberList As Range
Dim myCell As Range
Dim strSheetName As String

Set rngMemberList = Range("vbSheetExpandMemberList")

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each myCell In rngMemberList
strSheetName = myCell.Value
'copy the template worksheet and then give it the name contained
in the sheetName variable.
Sheets("wsToCopy").Copy 'Befo=Sheets("Sum End")
Sheets("wsToCopy (2)").Name = sheetName
Next myCell

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub







Example:

NewSheetNames (named range)
A1: Sheet1
A2: AnotherSheet
A3: NewSheet

Click button, and vba creates three new sheets called:
1. Sheet1
2. AnotherSheet
3. NewSheet

Any pointers are greatly appreciated!

Sub makeshtsfromrngSAS()
Dim c As Range
For Each c In Range("myrng")
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = c
Next c
End Sub
=====
On Jan 10, 1:28*pm, John wrote:
On Jan 10, 11:18*am, John wrote: How could I use a named range to create a series of new worksheets?

Details:
I have a named range (A1:A3) called NewSheetNames. I want to create a
new worksheet for each value in the named range. (3 sheets in this
example).

I found some code that got me started. Think this should work, but if
there's a better way I'm all ears!

Sub Create_Worksheets()

* * Dim rngMemberList As Range
* * Dim myCell As Range
* * Dim strSheetName As String

* * Set rngMemberList = Range("vbSheetExpandMemberList")

* * Application.ScreenUpdating = False
* * Application.EnableEvents = False

* * For Each myCell In rngMemberList
* * * * strSheetName = myCell.Value
* * 'copy the template worksheet and then give it the name contained
in the sheetName variable.
* * * * Sheets("wsToCopy").Copy 'Befo=Sheets("Sum End")
* * * * Sheets("wsToCopy (2)").Name = sheetName
* * Next myCell

* * Application.ScreenUpdating = True
* * Application.EnableEvents = True

End Sub







Example:

NewSheetNames (named range)
A1: Sheet1
A2: AnotherSheet
A3: NewSheet

Click button, and vba creates three new sheets called:
1. Sheet1
2. AnotherSheet
3. NewSheet

Any pointers are greatly appreciated!

Sub Add_Sheets()
Dim rCell As Range
For Each rCell In Range("A1:A3")
With Worksheets.Add(after:=Worksheets(Worksheets.Count) )
.Name = rCell.Value
End With
Next rCell
End Sub


Gord

On Tue, 10 Jan 2012 11:18:56 -0800 (PST), John
wrote:

How could I use a named range to create a series of new worksheets?

Details:
I have a named range (A1:A3) called NewSheetNames. I want to create a
new worksheet for each value in the named range. (3 sheets in this
example).

Example:

NewSheetNames (named range)
A1: Sheet1
A2: AnotherSheet
A3: NewSheet

Click button, and vba creates three new sheets called:
1. Sheet1
2. AnotherSheet
3. NewSheet

Any pointers are greatly appreciated!

Better yet..........a variable range of names in Column A

Sub Add_Sheets22()
Dim rCell As Range
Dim rng As Range
Set rng = Range(Cells(1), Cells(Rows.Count, 1).End(xlUp))
For Each rCell In rng
With Worksheets.Add(after:=Worksheets(Worksheets.Count) )
.Name = rCell.Value
End With
Next rCell
End Sub


Gord

On Tue, 10 Jan 2012 14:44:37 -0800, Gord Dibben
wrote:

Sub Add_Sheets()
Dim rCell As Range
For Each rCell In Range("A1:A3")
With Worksheets.Add(after:=Worksheets(Worksheets.Count) )
.Name = rCell.Value
End With
Next rCell
End Sub


Gord

On Tue, 10 Jan 2012 11:18:56 -0800 (PST), John
wrote:

How could I use a named range to create a series of new worksheets?

Details:
I have a named range (A1:A3) called NewSheetNames. I want to create a
new worksheet for each value in the named range. (3 sheets in this
example).

Example:

NewSheetNames (named range)
A1: Sheet1
A2: AnotherSheet
A3: NewSheet

Click button, and vba creates three new sheets called:
1. Sheet1
2. AnotherSheet
3. NewSheet

Any pointers are greatly appreciated!