Seeking method to name a worksheet, limited to 31 characters

[auto.pilot]

I have this small bit of code which is part of a much larger project. It simply re-names each worksheet to the value of cell B1 on that sheet.

    For Each ws In ActiveWorkbook.Worksheets
        ws.Name = Range("B1").Value
    Next ws

Today, I came across a value in cell B1 that is 32 characters. Sheet names are limited to a maximum of 31 characters.

What can I add to this code? I'd like to use only the first 31 characters in cell B1 as the new sheet name. I can't change the value in cell B1, this needs to be part of the code.

Thanks in advance.

Jim

 

[Joe4]

Just use the LEFT function to take the first 31 characters, i.e.

ws.Name = Left(Range("B1"),31)

 

[auto.pilot]

Just use the LEFT function to take the first 31 characters, i.e.

ws.Name = Left(Range("B1"),31)

Perfect. Thanks!

 

[Joe4]

You are welcome.