shorten sheet name
 

Hi,
I have the following code that is working well to duplicate several
time a "Template" worksheet based on a specific list on a "Config"
worksheet. The code is also changing the name of the worksheet to be
the same as the list. However, the name is sometimes too long to fit
the excel worksheet name. Is there any way to have the name to take
only the first 30 characters?
Thanks in advance for your help.

With ActiveSheet
.Name = myCell.Value
-------------------------------------------------------

Sub CreateNameSheets()
Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range
Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("Config")
With ListWks
Set ListRng = .Range("B6", .Cells(.Rows.Count,
"B").End(xlUp))
End With
For Each myCell In ListRng.Cells
TemplateWks.Copy after:=Worksheets(Worksheets.Count)
On Error Resume Next
With ActiveSheet
.Name = myCell.Value
.Range("B4").Value = myCell.Value
End With
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell
End Sub

shorten sheet name
 

Pls, do NOT post in more than one group

On Sep 21, 10:15*am, Norvascom wrote:
Hi,
I have the following code that is working well to duplicate several
time a "Template" worksheet based on a specific list on a "Config"
worksheet. The code is also changing the name of the worksheet to be
the same as the list. However, the name is sometimes too long to fit
the excel worksheet name. Is there any way to have the name to take
only the first 30 characters?
Thanks in advance for your help.

* * * * With ActiveSheet
* * * * * * .Name = myCell.Value
-------------------------------------------------------

Sub CreateNameSheets()
* * Dim TemplateWks As Worksheet
* * Dim ListWks As Worksheet
* * Dim ListRng As Range
* * Dim myCell As Range
* * Set TemplateWks = Worksheets("Template")
* * Set ListWks = Worksheets("Config")
* * With ListWks
* * * * Set ListRng = .Range("B6", .Cells(.Rows.Count,
"B").End(xlUp))
* * End With
* * For Each myCell In ListRng.Cells
* * * * TemplateWks.Copy after:=Worksheets(Worksheets.Count)
* * * * On Error Resume Next
* * * * With ActiveSheet
* * * * * * .Name = myCell.Value
* * * * * * .Range("B4").Value = myCell.Value
* * * * End With
* * * * If Err.Number < 0 Then
* * * * * * MsgBox "Please fix: " & ActiveSheet.Name
* * * * * * Err.Clear
* * * * End If
* * * * On Error GoTo 0
* * Next myCell
End Sub

shorten sheet name
 

On Sep 21, 11:30*am, Don Guillett wrote:
Pls, do NOT post in more than one group

On Sep 21, 10:15*am, Norvascom wrote:



Hi,
I have the following code that is working well to duplicate several
time a "Template" worksheet based on a specific list on a "Config"
worksheet. The code is also changing the name of the worksheet to be
the same as the list. However, the name is sometimes too long to fit
the excel worksheet name. Is there any way to have the name to take
only the first 30 characters?
Thanks in advance for your help.

* * * * With ActiveSheet
* * * * * * .Name = myCell.Value
-------------------------------------------------------

Sub CreateNameSheets()
* * Dim TemplateWks As Worksheet
* * Dim ListWks As Worksheet
* * Dim ListRng As Range
* * Dim myCell As Range
* * Set TemplateWks = Worksheets("Template")
* * Set ListWks = Worksheets("Config")
* * With ListWks
* * * * Set ListRng = .Range("B6", .Cells(.Rows.Count,
"B").End(xlUp))
* * End With
* * For Each myCell In ListRng.Cells
* * * * TemplateWks.Copy after:=Worksheets(Worksheets.Count)
* * * * On Error Resume Next
* * * * With ActiveSheet
* * * * * * .Name = myCell.Value
* * * * * * .Range("B4").Value = myCell.Value
* * * * End With
* * * * If Err.Number < 0 Then
* * * * * * MsgBox "Please fix: " & ActiveSheet.Name
* * * * * * Err.Clear
* * * * End If
* * * * On Error GoTo 0
* * Next myCell
End Sub- Hide quoted text -

- Show quoted text -

Sorry I intended to post in this group. My mistake.

shorten sheet name
 

It's easy to truncate the name at 30 characters. If the name length is less
than 31 Left returns the name unchanged so this works for all names.

.Name = Left(myCell.Value, 30)
.Range("B4").Value = .Name

"Norvascom" wrote in message
...
On Sep 21, 11:30 am, Don Guillett wrote:
Pls, do NOT post in more than one group

On Sep 21, 10:15 am, Norvascom wrote:



Hi,
I have the following code that is working well to duplicate several
time a "Template" worksheet based on a specific list on a "Config"
worksheet. The code is also changing the name of the worksheet to be
the same as the list. However, the name is sometimes too long to fit
the excel worksheet name. Is there any way to have the name to take
only the first 30 characters?
Thanks in advance for your help.

With ActiveSheet
.Name = myCell.Value
-------------------------------------------------------

Sub CreateNameSheets()
Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range
Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("Config")
With ListWks
Set ListRng = .Range("B6", .Cells(.Rows.Count,
"B").End(xlUp))
End With
For Each myCell In ListRng.Cells
TemplateWks.Copy after:=Worksheets(Worksheets.Count)
On Error Resume Next
With ActiveSheet
.Name = myCell.Value
.Range("B4").Value = myCell.Value
End With
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell
End Sub- Hide quoted text -

- Show quoted text -

Sorry I intended to post in this group. My mistake.

shorten sheet name
 

It's easy to truncate the name at 30 characters. If the name length
is less than 31 Left returns the name unchanged so this works for
all names.

.Name = Left(myCell.Value, 30)
.Range("B4").Value = .Name

It might be a good idea to Trim that first assignment just in case the
truncated text ends with a space character... no sense leaving the blank on
the end to trip up future references to the sheet name.

..Name = Trim(Left(myCell.Value, 30))

Rick Rothstein (MVP - Excel)