Change activesheet name and sheets added dynamically
Results 1 to 3 of 3

Thread: Change activesheet name and sheets added dynamically

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change activesheet name and sheets added dynamically

    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

  2. #2
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change activesheet name and sheets added dynamically

    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...
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,112
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Change activesheet name and sheets added dynamically

    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
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •