How do I rename multiple sheet sets?

[dsexpress]

Hi all,
First timer here. I am given a workbook with hundreds of sheets with different names - air(2),air(4), ground(4), ground(28),ground(6), etc. I need to keep the text of the name but change the number associated with it. -air(1),air(2), ground(3), ground(4),ground(5). I am trying to consecutively number tabs without changing the text then add the number in a cell on that sheet. The renaming part of the following code seems to work, but- If Sheets(i).Name= "whatever" doesn't. Your help would be appreciated.

Please Login or Register  to view this content.

[dominicb]

Good afternoon dsexpress

...and welcome to the forum!!

This shouldn't be too difficult if the number is always contained in brackets - we just have to find the position of the opening parenthesis "(", and take the name of the sheet from the left of that character, and rebuild the end bit with an incrementing number in the brackets. Try this :

Please Login or Register  to view this content.

HTH

DominicB

[Richard Buttrey]

Good afternoon dsexpress

...and welcome to the forum!!

This shouldn't be too difficult if the number is always contained in brackets - we just have to find the position of the opening parenthesis "(", and take the name of the sheet from the left of that character, and rebuild the end bit with an incrementing number in the brackets. Try this :

Please Login or Register  to view this content.

HTH

DominicB

Hi, the original post made reference to the sheet number being stored in a cell on each sheet. Assuming the cell is consistent within each of the Air, Ground, ..whatever sheets, the following should work. The select case statement obviously wants adding to for the ...'whatever' sheets which presumably have a different cell reference which contains the sheet number.

Please Login or Register  to view this content.

[jindon]

try

Please Login or Register  to view this content.

[dsexpress]

If I bring named sheets into the workbook consecutively the code works. When I then move the order of the sheets within the book naming error appears. Can I change the sheet number without changing the name?