Worksheet tab character limit

[Jak]

Hi

I am using the code below to rename worksheet tabs based on the value in cell B3 on each worksheet. I am getting a run time error attributed to the value in cell B3 exceeding 31 characters. This is the maximum number of characters a worksheet tab can support. Could anyone help with some additional code to rename the worksheet tabs and trim the name when 31 character limit is reached? Please note I need to keep the value in cell B3 as it is but trim the worksheet tab name only. I hope this is clear and someone can help me with this.

For I = 1 To Sheets.Count
    If Worksheets(I).Range("B3").Value <> "" Then
Sheets(I).Name = Worksheets(I).Range("B3").Value
    Else:
        Sheets(I).Name = "Default (" & I & ")"
    End If
  Next

 

[Andrew Poulsom]

How about?

Sheets(I).Name = Left(Worksheets(I).Range("B3").Value, 31)

 

[Jak]

Hi Andrew

Thanks for the quick reply. Just tested it and it works a treat. Great stuff.

 

[precept182]

How about?

Sheets(I).Name = Left(Worksheets(I).Range("B3").Value, 31)

do you know if I could limit it to a (), on my sheets I am renaming the sheets as to what is in B4 for example Heritage Foods (169000-004) how can I limit it to stop at the ( ? so that the sheet is named Heritage Foods

 

[Rick Rothstein]

do you know if I could limit it to a (), on my sheets I am renaming the sheets as to what is in B4 for example Heritage Foods (169000-004) how can I limit it to stop at the ( ? so that the sheet is named Heritage Foods

You could use this line of code...

Sheets(I).Name = Trim(Split(Worksheets(I).Range("B4").Value, "(")(0))