Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Even though one can rename worksheets, there is always a name I see that matches the order in whcih they were created ("Sheet1", "Sheet2" etc.). How do I get this name in VBA?

    I need to delete the first worksheet in a workbook (the one that was created when I created the WB)...

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is this what you want ?

    ActiveWorkbook.Worksheets(1).Name
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Francois View Post
    Is this what you want ?

    ActiveWorkbook.Worksheets(1).Name
    I believe so, thank you!

    Update - I have to come back to this one. I looked at jscher's response and tested your suggestion. It's not what I'm looking for as this syntax indeed returns the first sheet in the physical order. What I need to know is which sheet was there first when I created the WB. "Activesheet.Codename" indeed gives this 'original name' (I too look in the local-window for that). Problem that remains is how to find the first one... maybe just searching it is the only way?
    Something like:

    For i = 1 To Sheets.Count
    If Sheets(i).CodeName = "Sheet1" Then Debug.Print "Original sheet is: " & Sheets(i).Name
    Next

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by ErikJan View Post
    Even though one can rename worksheets, there is always a name I see that matches the order in whcih they were created ("Sheet1", "Sheet2" etc.). How do I get this name in VBA?
    I like to use the Locals window for discovering properties of objects.

    It appears that every worksheet object has a CodeName property which is in the original format ("Sheet1" etc.). The index to the worksheets collection corresponds to the current physical order, regardless of any earlier physical order. Between those, I think you can solve it.

    == Edit ==

    When I open so many tabs at once, sometimes I miss the fact that there was already an answer posted.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That name is known as the CodeName. It can be changed in the properties window in the VBE when you select the sheet in the project explorer.

    To find a sheet by its codename, you need code like this:

    Code:
    Public Function GetWorksheetFromCodeName(sShtCodeName) As Worksheet
    '-------------------------------------------------------------------------
    ' Procedure : GetWorksheetFromCodeName
    ' Company   : JKP Application Development Services (c)
    ' Author    : Jan Karel Pieterse (www.jkp-ads.com)
    ' Created   : 15-10-2009
    ' Purpose   : Returns the sheet object belonging to the codename passed.
    '-------------------------------------------------------------------------
        Dim oSh As Object
        On Error Resume Next
        For Each oSh In ThisWorkbook.Worksheets
            If oSh.CodeName = sShtCodeName Then
                Set GetWorksheetFromCodeName = oSh
                Exit Function
            End If
        Next
    End Function
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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