Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Need SheetName handling help :)

    Hi Guys,

    Looking for help with handling sheet names this time. I am trying to place the name of the sheet into a cell but the sheet name can have several characters that can change over time, however the number will always be the same and unique.

    ------------------------------------------------------------------------------------------------------
    - How can I use a VBA to extract the number from the ActiveSheet.Name and place it on "Stock" into cell "A1"?

    Example of possible sheet names:
    1
    +1
    1+
    @1
    #1


    ------------------------------------------------------------------------------------------------------
    - How can I do the opposite and use a VBA to find the SheetName with the matching number in it and

    Dim shtN As Worksheet
    Set shtN = Worksheets("SheetName")

    Thank you for looking
    Ferenc

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Ferenc

    You could add this function to a general vba module:
    Code:
    Function tabName()
    
    Application.Volatile
    
    zName = ActiveSheet.Name
    
    zName = Replace(zName, "+", "")
    zName = Replace(zName, "@", "")
    zName = Replace(zName, "#", "")
    
    tabName = zName
    
    End Function
    ..then in any cell on any sheet, use the formula
    =tabName()
    ..to return the current sheet name

    You could probably use the vba 'Like' operator to search current worksheets for the tab that contains a specified number

    zeddy
    •Eggler Tester
    .
    Last edited by zeddy; 2015-07-18 at 13:07.

  3. #3
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Got the first part.
    Thanks Zeddy...

    Can the second part be this easy or am I missing something?

    Code:
    Sub FindSheet()  
    
    Dim ws As Worksheet
    LID = Target.Offset(0, 3)
    
        For Each ws In ThisWorkbook.Sheets
            If ws.Name Like LID Then
                ws.Select
                MsgBox "Found it!"
            End If
        Next
    End Sub
    Ferenc
    Last edited by Ferenc Nagy; 2015-07-18 at 13:54.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Use zeddy's function in your code. BTW, target range variable will not function in this code. You will need another method to find the value for LID. Maybe something like LID = ActiveCell.Offset(0, 3)

    Code:
    Sub FindSheet()
    Dim ws As Worksheet
    LID = ActiveCell.Offset(0, 2)
    
        For Each ws In ThisWorkbook.Sheets
            X = tabName()
            If X = LID Then
                ws.Select
                MsgBox "Found it!"
            End If
        Next
    End Sub
    Last edited by Maudibe; 2015-07-18 at 15:24.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Ferenc

    I don't think it's as simple as that. I am assuming you can't simultaneously have two tabnames like [+9] and [9@] at the same time.
    ..and you must be careful in vba that Sheet(7) is NOT the same as Sheet("7").

    So, for example, if LID = "9", then the possible sheets could be:
    [9]; [+9]; [9+];[@9]; [9@];[#9];[9#]
    ..but you don't want to match with sheets that just 'contain' a "9" e.g NOT like
    [19];[29+];[#19] etc etc etc.

    So, something like this perhaps would get the sheet selected..
    Code:
    sub findSheet()
    zCurrent = activesheet.name
    on error resume next
    LID = ActiveCell.Offset(0, 2) 	'e.g "14"
    sheets(""& LID).select
    sheets("+"& LID).select
    sheets("#"& LID).select
    sheets("@"& LID).select
    sheets(LID & "+").select
    sheets(LID & "#").select
    sheets(LID & "@").select
    on error goto 0			'reset error trap
    if activesheet.name = zCurrent Then
    MsgBox "Sheet " & LID " NOT Found!"
    end if
    
    end sub
    zeddy
    •Cyber Response Team Mapping Associate, Latin America
    .

  6. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I will give yours a try in a minute Zeddy,
    Meantime I just got this working in a Worksheet_change sub:

    By simplifying the SheetName down to numbers only, I can get an exact match...

    Code:
    MsgBox "Here we go..."Dim ws As Worksheet, shtN
    MsgBox ActiveCell
    LID = Target.Offset(0, 3)                                           'LID = 3
    
    
        For Each ws In ThisWorkbook.Sheets
            shtName = ws.Name                                          'get AcitveSheet.Name
            shtName = Replace(shtName, "+", "")                                 'delete +
            shtName = Replace(shtName, "@", "")                                 'delete @
            
            If shtName = LID Then
                MsgBox "Got it!"
                ws.Select
                Exit For
            End If
        Next
    
    
    Exit Sub

    Thank you every for the help!

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    Here is some code which should work in more situations with out adjustments. By using a regular expression find you can find a string of numbers ignoring all special characters & letters. HTH
    Code:
    Option Explicit
    
    Sub FindSheet()
    
       Dim wksCur As Worksheet
       Dim zLID   As String
       
       zLID = 123   '*** Change this to pull your value ***
       
       For Each wksCur In ActiveWorkbook.Sheets
          If zLID = ExtractNo(wksCur.Name) Then
            MsgBox "Found Sheet is: " & wksCur.Name
            Exit For
          End If
       Next wksCur
       
    End Sub   'Find Sheet
    
    Function ExtractNo(ByVal text As String) As String
    
       Dim zResult     As String
       Dim allMatches As Object
       Dim RegEx      As Object
       
       Set RegEx = CreateObject("vbscript.regexp")
    
       With RegEx
           .Pattern = "(\d+)"   '*** Find a string of numbers ***
           .Global = True
           .IgnoreCase = True
           Set allMatches = .Execute(text)
       End With 'RegEx
       
       If allMatches.Count <> 0 Then
         zResult = allMatches.Item(0).submatches.Item(0)
       End If
    
       ExtractNo = zResult
    
    End Function   'ExtractNo
    Sample:
    Ference.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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