Changing Tab Names VBA

Tom98

New Member
Joined
Oct 26, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
Try:
VBA Code:
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
 
Last edited:

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
357
Office Version
  1. 2016
Platform
  1. Windows
Hi Tom,

put this into the thisworkbook module:
VBA Code:
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:
VBA Code:
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

New Member
Joined
Oct 26, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,307

ADVERTISEMENT

Try:
VBA Code:
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

New Member
Joined
Oct 26, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,307

ADVERTISEMENT

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

New Member
Joined
Oct 26, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
Try:
VBA Code:
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

New Member
Joined
Oct 26, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,920
Messages
5,638,995
Members
417,061
Latest member
thematulaak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top