Create value then rename sheets based on value

[ryan.johnson]

I have a workbook that is about 60 sheets long that I receive and each sheet is conveniently named Sheet1, Sheet2, etc. and each sheet is formatted identically except for the first two and last one. I'm looking to create a macro that will go through and take the left six characters of the cell A3 and rename the sheet that value.

I've tried the following, but it doesn't seem to loop properly and the first two sheets and last sheet I have are cover sheets and have no values in A3 and because of that, my macro errors out as well.

Please Login or Register  to view this content.

[AB33]

This takes the first 6 letter of each sheet and name it with that letter.

Please Login or Register  to view this content.

[HaHoBe]

Hi, ryan.johnson,

maybe like this (no check for illegal characters or more than 31 characters here):

Please Login or Register  to view this content.

Ciao,
Holger

[ryan.johnson]

I modified your formula slightly (I accidentally said left earlier, but I'm actually looking for right) and if I remove the first two sheets and the last sheet it works perfectly. I'm looking to have it exclude the first two sheets and the last one, if possible. The number of sheets changes every so often though.

Please Login or Register  to view this content.

[AB33]

"I'm looking to have it exclude the first two sheets and the last one, if possible"

If they have names, you can use names to exclude them as shown with sheet1.

Please Login or Register  to view this content.

' adjust if you want to exclude sheet1 from the loop

If this is no the option, you can use pattern in the names (Or have unique identifier) to either include or exclude certain names from sheet collection.