Change activesheet name and sheets added dynamically

[gracecyl]

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

 

[sykes]

This will change the name of the ativesheet:

Sub chng_wsname()
ActiveSheet.Name = "WS1"
End Sub

...and this will automatically rename any new worksheets

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:

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...

 

[Norie]

Perhaps.

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