Change active sheet name

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Change active sheet name

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change active sheet name

     
    Good morning fellas!

    How can I change the code below

    Sub ChngSheetName()

    ActiveSheet.Name = "Template1"

    End Sub

    So that if "Template1" already exist in the workbook it will rename to "Template2" and if that exist to "Template3" and so on.

    Thanks

  2. #2
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,199
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change active sheet name

    This should work:

    Sub NameSheet()
    Dim NameDone As Boolean, ThisOneFound As Boolean
    Dim Suffix As Integer, i As Integer
    NameDone = False
    For Suffix = 1 To 100
    If NameDone Then Exit Sub
    ThisOneFound = False
    For i = 1 To ActiveWorkbook.Sheets.Count
    If Sheets(i).Name = ("Template" & Suffix) Then
    ThisOneFound = True
    i = ActiveWorkbook.Sheets.Count
    End If
    Next i
    If Not ThisOneFound Then
    ActiveSheet.Name = "Template" & Suffix
    NameDone = True
    End If
    Next Suffix
    End Sub
    Lift up your eyes, round about, and see

  3. #3
    Board Regular amigos's Avatar
    Join Date
    Sep 2003
    Location
    London
    Posts
    407
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change active sheet name

    That will name active sheet with Template & first free number

    so if you have Template1,Template2,Template3
    it will call the active one Template4

    when you have Template1,Template3,Template4
    it will call the active one Template2


    Sub RenameSheet()

    Dim i As Integer

    On Error GoTo Error_Handler

    i = 1

    Start:

    ActiveSheet.Name = "Template" & i

    Exit Sub

    Error_Handler:
    i = i + 1
    Resume Start

    End Sub



    I hope it will help you
    Amigos
    .: wisdom denotes the pursuing of the best ends by the best means :.

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Posts
    127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change active sheet name

    Thanks Earlyd!

    Your code gives me this error message: (I already have Template1,2,3 and I'm running the code where the active sheet name is Template4)

    Run time error '1004':

    Cannot rename a sheet to the same name a another sheet, a referenced object library, or a workbook referenced by visual basic.

    Any ideas?

  5. #5
    Board Regular
    Join Date
    Jul 2002
    Posts
    127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change active sheet name

      
    Thanks Amigos,

    Your code is working!

    Thanks everyone!

User Tag List

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
  •  

 

 
DMCA.com