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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,100,045
Messages
5,472,157
Members
406,807
Latest member
Mr P

This Week's Hot Topics

Top