Change activesheet name and sheets added dynamically

gracecyl

New Member
Joined
May 21, 2018
Messages
9
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,650
Office Version
365
Platform
Windows
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...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,564
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,898
Messages
5,471,373
Members
406,759
Latest member
jackflint

This Week's Hot Topics

Top