Results 1 to 5 of 5

Change active sheet name

This is a discussion on Change active sheet name within the Excel Questions forums, part of the Question Forums category; Good morning fellas! How can I change the code below Sub ChngSheetName() ActiveSheet.Name = "Template1" End Sub So that if ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    127

    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

    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

    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

    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

    Default Re: Change active sheet name

    Thanks Amigos,

    Your code is working!

    Thanks everyone!

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