Creating a Find macro

Do you have a master list in that Workbook that you can select a name and then run a Macro which takes you to the other sheet in the same Workbook, that that same name in it?

Or do you want to type the name to find each time [you know it should be entered exactly the same as it is in the other location]?



Sub FindAcrossAll()

Dim What As Variant
Dim LastAddr As String
Dim LastSheet As String
Dim NeverFound As Boolean

NewSearch:
NeverFound = True

What = Application.InputBox("What are you looking for?")

If What = False Then Exit Sub

For Each Sht In Worksheets
Sht.Activate

Set Found = Sht.UsedRange.Find(What)

If Not Found Is Nothing Then ' The value has been found.
FirstAddress = Found.Address
Do

Found.Activate
LastAddr = Found.Address
LastSheet = Sht.Name
NeverFound = False
Msg = "Keep on searching?"
Title = "Continue ?"

Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then ' Doesn't want to continue
MsgBox "Search cancelled."
Found.Activate
Exit Sub ' Quit the macro

End If

Set Found = Cells.FindNext(After:=ActiveCell)

If Found.Address = FirstAddress Then Exit Do

Loop

End If
Next Sht

If NeverFound Then ' Nothing found
ln1 = "The string " & Chr(34) & What & Chr(34) & " was not found !" & vbCrLf
ln2 = "Do you want to start a new search?"
Msg = ln1 & ln2

Style = vbYesNo + vbCritical + vbDefaultButton2
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
MsgBox ("Search ended")
Exit Sub
Else
GoTo NewSearch
End If
Else

Msg = "Search complete. Do you want to go back to last found ?"
Style = vbYesNo + vbDefaultButton2
Title = "Search Complete"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
Sheets(LastSheet).Select
Range(LastAddr).Select
Exit Sub
Else

Msg = " Do you want to start a new search?"
Title = "What Next ?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
MsgBox ("Search has ended")
Exit Sub
Else

GoTo NewSearch
End If
End If
End If
End Sub

They are lists of stocks and industries so there is no master list and the sheets are always expanding. I have a main page where I want to be able to type in a company name and then have it jump to the worksheet that that company is located on. It is fine if i have to type it in exactly the same. Any suggestions?

That works great!!!! Thank you so much

The code I posted will lookup partial data strings or values!
If you have more than one location it will find them all, but you will need to quit to do any thing at that location.

To build another Sub from the one I posted to continue using the current cell selections value as the string to search for rather that type or paste that name again. You can make a copy of the Sub, change its name and change one line of code, to do this. Then you will have two sets of code to search both conditions!

In the code, near the top, look for:

What = Application.InputBox("What are you looking for?")

and change this line to:

What = ActiveCell.Value
or
What = Selection.Value