Need to loop
 

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

Need to loop
 

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" wrote in message
ups.com...
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

Need to loop
 

On Jun 14, 2:39 pm, "PCLIVE" wrote:
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" wrote in message

ups.com...



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- Hide quoted text -

- 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

Need to loop
 

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" wrote in message
oups.com...
On Jun 14, 2:39 pm, "PCLIVE" wrote:
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" wrote in message

ups.com...



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- Hide quoted text -

- 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

Need to loop
 

On Jun 15, 6:55 am, "PCLIVE" wrote:
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" wrote in message

oups.com...



On Jun 14, 2:39 pm, "PCLIVE" wrote:
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" wrote in message

roups.com...

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- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -

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

Greg