Rename Sheets in a Workbook

[CoSinus]

Hi Everyone,
the following code gives all worksheets in a workbook the name of the string found in cell A1.

But I have the problem that I have sheets which have only one word. Then the script does not work anymore.

Also I would like to have the complete content of cell A1 as sheet name no matter what is in it.

Maybe you can help me to adjust the code to use all characters in cell A1 as sheet names of the respective sheet.

Here is the Code:

Please Login or Register  to view this content.

[vba_php]

the reason it doesn't work is because you spec'd the delimiter as a space using split(). that function only does one thing.
you can't make it do anymore. the best workaround, IMO, is to create sheets that do you not have spaces in them. that is, after all,
protocol for all developers anyway. spaces create very bad issues. that's why SEO addresses on websites do not have spaces in them.
they have dashes (-) or underscores (_).

[Croweater]

What's wrong with just using this line?

Please Login or Register  to view this content.

So this would be your entire code..

Please Login or Register  to view this content.

[CoSinus]

Thank you for your help:
It gives me an error in this line:

Please Login or Register  to view this content.

And there is a Problem with sheetnames with only one word.
Is there a chance that you can add underscores (_) in the sheetname.
I would not mind that.
But I am just a layman.

As an alternative solution 2 words should also be enough. But I also have texts in A1, which contain only one word.
And then the vb a doesn't work no more.

Thank you for your help.

[jindon]

Some possible error for renaming sheet.

1) Can't use the name that already exists in the workbook.
2) Length must be less than 31 characters.
3) Use of invalid characters i.e. : ? \ [ ] / *

If you don't care about above then simply

Please Login or Register  to view this content.

[Croweater]

Thank you for your help:
It gives me an error in this line:

Please Login or Register  to view this content.

What is the error? Can you post a workbook to demonstrate that error?

And there is a Problem with sheetnames with only one word.

Did you take out the line where you are trying to SPLIT the cell using the space (as I posted above)?
If you copy the code I pasted this should not be a problem.

[CoSinus]

@jindon:

Here is an error in

Please Login or Register  to view this content.

I send you the excel file.

As you can see in the sample Sheet the Sheetnames do not carry the strings in A1.

Thanks all for your help so much.

[sintek]

Some of the A1 cells have char > 31...
Time Spent on Decisions by Teams = 32 Char

Please Login or Register  to view this content.

[jindon]

As I mentioned already, length...

Please Login or Register  to view this content.

[CoSinus]

Thank you all for your help.
The solution in the end is perfect.

I am very grateful.

[CoSinus]

I know that the thread was closed,
but I have worked on a code with your help that could deal with the problem of the string length.
The Code has flaws. Could you please help me.

To test it I attached a document that has too long names in A! on some sheets which are partly the same.

The goal of the code should be to cut off the characters and to add a numbering in case of duplicate designations, so that no error message appears.

I am glad about your help to correct the code.

Thanks a lot

Please Login or Register  to view this content.

[jindon]

This is atypical human error when you did not enable Option Explicit to force the variables to be declared.
If you add Option Explicit at the top of the module, it will tell you the variable(s) that are not declared.

[CoSinus]

Thanks for the Tip.
I did it and made changes.
I guess it is almost working.

Here is the revised vba:

Please Login or Register  to view this content.

Small error with

Please Login or Register  to view this content.

Maybe someone can check please.
Thanks everybody!

[sintek]

Play around with this...

Please Login or Register  to view this content.

[jindon]

CoSinus

Refer to my Post#5 for Error renaming sheet.

Please Login or Register  to view this content.

[CoSinus]

Thank you very much.
I have been looking for a solution to this problem for so long.

THANKS!!!