Change worksheet name based on prompt

[thedon_1]

I have a workbook with multiple sheets and want them to be renamed based on the value of a certain cell, e.g A5 in each sheet.

I want to add a prompt so that the user can select a cell, e.g B6, and then it will use that cell from all of the sheets to rename.

This is the code I have but it doesn't work.

Please Login or Register  to view this content.

Also, would there be a way to use the cell value to replace part of the current sheet name?

E.g my sheet is currently called sheet 1 (1), i want the last 3 charachters removed, and then the value of cell B6 added to the name instead of replaced.

Thanks

[StephenR]

Try changing this line:

Please Login or Register  to view this content.

[thedon_1]

I get

runtime error 91
Object variable or with block variable not set.

It then refers to this bit of code.

Please Login or Register  to view this content.

Any ideas?

[StephenR]

Yes. A range is an object and object variables need to be assigned using the Set command:

Please Login or Register  to view this content.

[thedon_1]

So how would I implement the code to do what i need?

Sorry, just a bit stuck here.