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.

Sub Search_Rename()
Dim i As Long
For i = 1 To Worksheets.Count
 i = i + 1
 If Sheets(i ).Name = air(n) Then Sheets(i ).Name = Sheets(i).Name = "air(" & i & ")"  'This line I can't figure out syntax for if statement air(n)
     Cell(3,4)=i
If Sheets(i ).Name = Gound(n) Then Sheets(i ).Name = Sheets(i).Name = "ground(" & i & ")"  'This line I can't figure out syntax for if statement ground(n)
      Cell(8,9)=i
Next i
End Sub

[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 :

Sub test()
n = 1
For Each sht In ActiveWorkbook.Worksheets
a = InStr(1, sht.Name, "(")
sht.Name = Left(sht.Name, a - 1) & "(" & n & ")"
n = n + 1
Next sht
End Sub

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 :

Sub test()
n = 1
For Each sht In ActiveWorkbook.Worksheets
a = InStr(1, sht.Name, "(")
sht.Name = Left(sht.Name, a - 1) & "(" & n & ")"
n = n + 1
Next sht
End Sub

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.

Sub Search_Rename()
Dim shWs As Worksheet

For Each shWs In ActiveWorkbook.Sheets 
shWs.Activate
Select Case Left(shWs.Name, 3)
    Case "Air"
        shWs.Name = "Air(" & Cells(3, 4) & ")"
        
    Case "Gro"
        shWs.Name = "Ground(" & Cells(8, 9) & ")"

End Select

Next shWs
End Sub

[jindon]

try

Sub Page_Num()

Dim n As Integer, myCol As Integer, flg As Boolean
Dim OldSht As Worksheet
n = 1
For Each OldSht In ActiveWorkbook.Sheets
    Select Case Left(OldSht.Name, 3)
        Case "AIR", "EXH" : myCol = 7 : flg = True
        Case "DIF", "STA" : myCol = 13 : flg = True
    End Select
    If flg Then
        OldSht.Name = Left(OldSht.Name, 3) & "(" & n & ")"
        OldSht.Cells(5, myCol).Value = n
        n = n + 1
    End If
    flg = False
Next OldSht
End Sub

[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?