Changing Tab Names VBA

[Tom98]

Hi,

If I wanted to go through each sheet and rename the tab names as the previous sheet plus 1, how would I go about doing this? Ie if I had sheet1, sheet2, sheet3 and sheet4, and then I were to delete sheet3 I would then like a macro to run to update the remaining sheets as sheet1, sheet2, sheet3. Any help with this would be great!

TIA

 

[mumps]

Try:

Sub ReNameSheets()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 1 To Sheets.Count
        Sheets(x).Name = "Sheet" & x
    Next x
    Application.ScreenUpdating = True
End Sub

 

[CsJHUN]

Hi Tom,

put this into the thisworkbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
For i = 1 To Sheets.Count
Sheets(i).Name = "Sheet" & i
Next i
End Sub

With each sheet actication this will run over all your sheets, and rename it.
If you move sheets (like drag-and-drop) could cause errors. Bypass that use this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
For i = 1 To Sheets.Count
On Error Resume Next
Sheets(i).Name = "Sheet" & i
On Error GoTo -1
Next i
End Sub

 

[Tom98]

Thanks for the quick replies. How would I then get this to not consider the 5 specific sheets as I don't want it renaming all sheets. Just specifically all sheets to the left of the active sheet (the sheet with the button to update the tab names) I want to update. Cheers

 

[mumps]

Try:

Sub ReNameSheets()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 1 To ActiveSheet.Index - 1
        Sheets(x).Name = "Sheet" & x
    Next x
    Application.ScreenUpdating = True
End Sub

 

[Tom98]

Try:

Sub ReNameSheets()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 1 To ActiveSheet.Index - 1
        Sheets(x).Name = "Sheet" & x
    Next x
    Application.ScreenUpdating = True
End Sub

This doesn't seem to be working. My problem is updating the sheet numbers (so to Test 1, 2, 3, 4) right of the "Button sheet", with everything left of and including the button sheet to remain the same, don't know if this will help?

|Front Cover | User guide | References | Button Sheet | Test_1 | Test_2 | Test_3 | Test_5 |

Thanks

 

[mumps]

You said:

specifically all sheets to the left of the active sheet (the sheet with the button to update the tab names) I want to update

Are you now saying that you want the sheets to the right of the active sheet? Also, what are the actual sheet names? Do they all start with the word "Test"?

 

[Tom98]

You said:

Are you now saying that you want the sheets to the right of the active sheet? Also, what are the actual sheet names? Do they all start with the word "Test"?

Yes, sorry, my mistake. I meant the right of

Yes they are all "Test_" then a number.

Cheers

 

[mumps]

Try:

Sub ReNameSheets()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = ActiveSheet.Index + 1 To Sheets.Count
        Sheets(x).Name = "Test_" & x
    Next x
    Application.ScreenUpdating = True
End Sub

 

[Tom98]

Try:

Sub ReNameSheets()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = ActiveSheet.Index + 1 To Sheets.Count
        Sheets(x).Name = "Test_" & x
    Next x
    Application.ScreenUpdating = True
End Sub

Great thanks. This seems to start at number "5" and then count up (as it it tab 5), is there a way of starting it at 1 and counting up?