Code to rename worksheet based on cell value - text too long!

[wpryan]

Hi All,

I need to rename a worksheet based on a value in a cell. The text string is too long, however (the default string will be "yyyymmdd Rotator Test Data FLMxxxx"). I was thinking to add application.worksheetfunction to the code to get the left most characters (which are the most important ones), but the .left function is not available. How can I trim the text to exclude the "FLMxxxx" text from the sheet name? Thanks in advance...

 

[bferraz]

You can use a VBA code to rename the sheet.

Sub Rename()

Dim ws As Worksheet

Set ws = ActiveSheet

ws.Name = Left(ws.Range("A1").Value, 31)

End Sub

 

[HaHoBe]

Hi,

maybe

With ActiveSheet
  .Name = Left(Replace(.Range("A1").Value, " FLMxxxx", ""), 31)
End With

You should check for illegal characters as well (":", "/", "\", "?", "*", "[", "]").

Holger

 

[wpryan]

Duh my bad, it was simpler than I imagined...

shNew.Name = Left(Range("B1"), 30)