Need to loop

[GregR]

I have this code, which adds sheets to a workbook based on a named
range. It renames the sheets to the name in the range, but errors on
the second name change because it already exists. How do I get he
macro to rename sheets based on the names in the named list. TIA

Sub AddRangeOfSheets()

Dim c As Range

For Each c In Sheets("sheet1").Range("SList")
If Not IsEmpty(c) Then
CreateSheet (c.Value)
End If
Next c

End Sub

Sub CreateSheet(ShName As String)

Dim CreateSheet As Worksheet

On Error GoTo errh
Set CreateSheet = ActiveWorkbook.Worksheets(ShName)
Exit Sub

errh:
Set CreateSheet = ActiveWorkbook.Worksheets.Add
CreateSheet.Name = ShName

End Sub

Thanks, Greg

 

[PCLIVE]

There are parts of your code that I don't understand.

I'm assuming "SList" is a named range.
I'm assuming "CreateSheet" is an attempt to add a worksheet...or is it
trying to call another Macro?
My suggestion is below

Sub AddRangeOfSheets()
Dim c As Range

For Each c In Range("SList")
If Not IsEmpty(c) Then
Sheets.Add
ActiveSheet.Name = c.Value
End If
Next c

End Sub

HTH,
Paul

 

[GregR]

There are parts of your code that I don't understand.

I'm assuming "SList" is a named range.
I'm assuming "CreateSheet" is an attempt to add a worksheet...or is it
trying to call another Macro?
My suggestion is below

Sub AddRangeOfSheets()
Dim c As Range

For Each c In Range("SList")
If Not IsEmpty(c) Then
Sheets.Add
ActiveSheet.Name = c.Value
End If
Next c

End Sub

HTH,
Paul
















- Show quoted text -

Paul, my code and your code work just fine if there are no blank rows
in the named range, but there are. How do I overcome this hurdle?

Thanks

Greg

 

[PCLIVE]

Greg,
Try stepping through this code using F8 and find out exactly which line the
code fails on. I know you said that it fails when there are blank cells in
your range, but that doesn't happen on my end. My guess is that the cells
that appear empty are not actally empty and that the code fails on
"ActiveSheet.Name= c.Value". This would fail if the cell value contains an
illegal character for a Worksheet name. See if that's it.


Sub AddRangeOfSheets()
Dim c As Range

For Each c In Range("SList")
If Not IsEmpty(c) Then
Sheets.Add
ActiveSheet.Name = c.Value
End If
Sheets("Sheet1").Activate
Next c

End Sub

HTH,
Paul

 

[GregR]

Greg,
Try stepping through this code using F8 and find out exactly which line the
code fails on. I know you said that it fails when there are blank cells in
your range, but that doesn't happen on my end. My guess is that the cells
that appear empty are not actally empty and that the code fails on
"ActiveSheet.Name= c.Value". This would fail if the cell value contains an
illegal character for a Worksheet name. See if that's it.

Sub AddRangeOfSheets()
Dim c As Range

For Each c In Range("SList")
If Not IsEmpty(c) Then
Sheets.Add
ActiveSheet.Name = c.Value
End If
Sheets("Sheet1").Activate
Next c

End Sub

HTH,
Paul







- Show quoted text -

Paul, that was it. I had an illegal character in the range for naming
sheets. Thanks

Greg