Renaming worksheet question

[MrSandman]

I have a workbook that has multiple worksheets. What i would like to do is rename Sheet2 to a name based on the value from a cell from Sheet1. In addition to that i would also like the 2nd half of the name a fixed value. For example If cell A1 in Sheet1 is changed to A12345 i would like Sheet2 to be renamed to "A12345-George" where the "George" part is fixed. All the other worksheets will follow the same naming convention for the prefix, however the suffix will have a different name. So Sheet3 will be "A12345-Sally" and Sheet4 will be "A12345-Jane" so on and so forth.

I used the search function and found some similar topics but like mine they were all very user specific.

[mikerickson]

This identifys sheets by their code name. (It assumes < 10 sheets)
As part of a Worksheet_Change event, each sheet's name will be linked to its cell A1.

Please Login or Register  to view this content.

[MrSandman]

This identifys sheets by their code name. (It assumes < 10 sheets)
As part of a Worksheet_Change event, each sheet's name will be linked to its cell A1.

Please Login or Register  to view this content.

Thanks for your response. One thing i probably failed to mention was that i would like to have the option of choosing which sheet has which suffix and i would also have more than 10 sheets. Is there a way to input this code for each sheet without using an array function?

[MrSandman]

Ok, things have changed slightly. Hopefully this makes it easier.

How would i change the worksheet name based on a reference to another cell? Also, i would like the change to occur immediately and NOT have to be manually run.

I've come up with this but it doesn't happen right away when cell A1 references values from another worksheet within the same workbook. Also, i don't want to apply this to all worksheets in the workbook. Just certain ones. I suppose i could just insert this code under each worksheet where it needs to be done. Can anyone help?:

Please Login or Register  to view this content.