Make Codename macro shorter & possibly more efficient

[Dbroek]

This code runs fine. However it is long and repetitive.
I'm believing there's a way to loop something like this to make it shorter but I don't know how just yet. How do I set

Please Login or Register  to view this content.

[jolivanes]

Please Login or Register  to view this content.

[achammar]

Run the macro in this test file. It doesn't care what the sheets are currently named or how many there are. It just renames all the sheets except the first one to the values you have starting in B2.

[Fluff13]

How about

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

P.S
You need to;
Go to
File --> Options --> Trust Center --> Trust Center Settings --> Macro Settings.
Check Trust Access to the VBA project object model.

[Dbroek]

Gurus, thank you for your generous offers. Unfortunately, I've come to learn that the code I've originally written does not work for what I intended after all. So, therefore none of you offers will have any impact on my problem. And, I am so sorry for posting my question before learning of this problem.

I will post the file for your review for you to see why but I fully understand if you decide to move on and not attempt any further suggestions.
Anyways, my original code does not work because it becomes corrupt when try to sort cells A1:E17 in ABC order or if I clear any of the values in cell B2:B17. The B-column cells are what I'm using to try and rename the worksheets in a reliable manner. I might still come up with an alternative option... but so far, am awfully stymied.

Again, I am gracious for your generosity. Thank you much for sharing. This is very educational for me.