shorten worksheet name



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
End If
On Error GoTo 0
Next myCell
End Sub

Don Guillett

With ActiveSheet
.Name = left(myCell,30)
.Range("B4").Value = left(myCell,30)
End With

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question