1
Sub searchBoys()
'profile is the name being searched
Dim profile As String
Dim ws As Worksheet

profile = LCase(Sheet8.Range("searchName").Value)

For Each ws In ActiveWorkbook.Worksheets
    If profile = Trim(LCase(ws.name)) Then
        ws.Select
        Exit Sub
    End If
Next ws

End Sub

Trying to do a small little project where I create an excel page containing fun facts about each of my friends. From the main page, the user would search their name, (referenced as profile) and the active worksheet would switch to the one pertaining to them.

nick
  • 11
  • 2
  • Does this help you? Change the IF to this on the right hand side... `Trim(LCase(ws.Name))` Also, fully qualify `Range` to the worksheet in question, that may cause you issues as well. – Skin Mar 17 '22 at 03:19
  • @Skin I just added both but I'm still getting Run-time error '1004, (Application-defined or object-defined error). Also, does it matter that for 'ws.Name', name always autocorrects to lowercase? – nick Mar 17 '22 at 03:25
  • Which line does the error occur on? – Skin Mar 17 '22 at 03:27
  • hmm, profile = LCase(Sheet8.Range("searchName").Value) – nick Mar 17 '22 at 03:29
  • I copied your code in and it works for me without issue. Try `.Text` instead of `.Value`. Last ditch attempt from me. Also, are you definitely on the `ActiveWorkbook` when you run the code? – Skin Mar 17 '22 at 03:32
  • 1
    thank you for your help, I misspelled the cell name outside of VBA.... I was looking at this for like 45 min prior to posting. Coding sometimes... – nick Mar 17 '22 at 03:33

1 Answers1

1

Select Matching Worksheet

  • ThisWorkbook means the workbook containing this code.
  • If a worksheet named after the Profile variable exists (case is irrelevant), it will be selected. Otherwise, nothing will happen.
Option Explicit

Sub SearchBoys()

    Dim Profile As String: Profile = CStr(Sheet8.Range("SearchName").Value)
    On Error Resume Next
        ThisWorkbook.Worksheets(Profile).Select
    On Error GoTo 0

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28