[Q] How to create new worksheets based on an array (via named range)

[John]

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!

 

[John]

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 'Before:=Sheets("Sum End")
Sheets("wsToCopy (2)").Name = sheetName
Next myCell

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[Don Guillett]

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
=====

 

[Gord Dibben]

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

 

[Gord Dibben]

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