Change activesheet name and sheets added dynamically

gracecyl

New Member
Joined
May 21, 2018
Messages
12
How do I create a code for changing my ActiveSheet to a name and subsequent added sheets with name +1 ?

E.g. WS1, WS2, WS3
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
This will change the name of the ativesheet:
Code:
Sub chng_wsname()
ActiveSheet.Name = "WS1"
End Sub
...and this will automatically rename any new worksheets
Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim cntr As Integer

cntr = Me.Sheets.Count
Sh.Name = "WS" & cntr
End Sub
... It MUST go into the "This Workbook" module, though!

However... this will only count the number of sheets, and give the new sheet the name of "WS" and the new number of sheets, once the new sheet has been added.
If you already had, say, 3 sheets in the WB, then named the NEXT one "WS1" THEN added a new one, the new one would be called "WS5" then "WS6" and so on.
To get over this, you could deduct the number of sheets in the WB before you start this renaming, and deduct that from "cntr" in my code - so, if you already had 4 sheets in the workbook, you'd amend the code thus:

Code:
cntr = Me.Sheets.Count -4
Bit schoolboy, but you can probably make it work for you.

Of course, there's nothing to stop other users messing it up, by changinbg sheet names etc etc...
 
Upvote 0
Perhaps.
Code:
Sub NameSheets
Dim cnt As Long
Dim I As Long

    For I = ActiveSheet.Index To ActiveSheet.Count
        cnt = cnt + 1
        Sheets(I).Name = "WS" & cnt
    Next I

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top