VBA to Name Wkst - Help with Removing special char. & invalid names

[popps]

Hi - I'm a VBA newbie, but am making a little progress. i have a macro (thanks to the help of this forum) to rename a wkst based on a cells value. It works great. But some of the cells have invalid wkst names, producing an error.

I want it to fix invalid wkst names (remove special characters & shorten if too long, etc). I found a script to remove special characters, but don't know how to incorporate into my existing code or how to shorten the name if its too long.

Existing Code:

Please Login or Register  to view this content.

Code i want to incorporate to remove special characters (have not tested it)

Please Login or Register  to view this content.

And then how would i get it to shorten to less than 32 characters?
Millions of thanks!

[jindon]

Example

Please Login or Register  to view this content.

[popps]

Hmmm, i tried it but it gave me an error when trying to rename it "1161 Stables Commercial Properties, LLC"

I tried adding a comma to the .Pattern line, but it didnt work

Please Login or Register  to view this content.

Any ideas?

[jindon]

Are you trying to rename after adding new sheet by hand?

If so,

1) The code is not running for that action.
2) Error because sheet name is too long.

Else
P2 of newly created sheet is always EMPTY.
So you will need to change Sh.Range("P2") to other sheet reference.

And the length should be less than 31, so

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

[popps]

You are right, the name was too long - i thought the char limit was 32 but it is 31. i changed it the code to 30 and it worked. Thanks so much jindon!!!!!!

Please Login or Register  to view this content.