Mocro to Loop through Workbook and Rename Tabs

[KeithG]

Each month I get a workbook with 30+ worksheets (Sheet1, Sheet2, Sheet3....Sheet30+). I was wondering if there is a macro that could loop through all 30 worksheets and rename them to the value in cell "U9". The value is U9 is unique for each worksheet. I tried searching the board but could not find something with my specific issues:

I don't create the worksheets in the workbook. They are already created by the time I receive it.

Sometimes the value in U9 contains invalid characters..\ / *
Sometimes the value in U9 contains more than 31 characters

I am okay with something that will skip the worksheets that contain invalid characters. I can do them manually. they are only small % of total worksheets. I would love something that would loop through each worksheet ant rename it to the value in cell "U9".

Any help???

 

[p45cal]

This should do it (untested):

Sub blah()
For Each sht In ActiveWorkbook.Sheets
    sht.Name = Replace(Replace(Replace(Replace(Replace(Replace(Replace(sht.Range("U9").Value, ":", ""), "/", ""), "\", ""), "?", ""), "*", ""), "[", ""), "]", "")
Next sht
End Sub

 

[p45cal]

Try for that long middle line:

    sht.Name = Left(Replace(Replace(Replace(Replace(Replace(Replace(Replace(sht.Range("U9").Value, ":", ""), "/", ""), "\", ""), "?", ""), "*", ""), "[", ""), "]", ""), 31)

 

[KeithG]

Thank yo so much p45cal. It worked great!