very large WB-what can I do to find a specific tab w/o scrolling?

[Remington]

I have a very large workbook with multiple tabs each represented
aphabetically with a client name. I want to do something to more efficiently
navigate through these instead of using the scroll buttons at the bottom. I
have a content page set up as a tab and thought I could run a macro or
something to bring me back to that, then click the client name from the list
to return to the desired tab, but I am having difficulty with this. Any
ideas?

 

[Luke M]

Assuming you have a list of the names (say, A2:A100) on your main sheet. On
your main sheet, right click the sheet tab, view code, paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
On Error Resume Next
Sheets(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such sheet exists.", vbCritical, _
"Sheet Not Found"
End If
On Error GoTo 0
End If
End Sub


If you want a fast-way to get back to the content sheet, simply record a
macro of you selecting your content sheet. Then, Under Tools-Macro-Macros,
assign your newly recorded macro to a shortcut key for ease of use.

 

[Remington]

Thanks Luke M. I had the same idea with getting back to the content page,
but am still unclear with how to reach specific sheets from the name on the
content form tab. I did what you said and did the right click on the content
main page and pasted the formula, but now what?

 

[FSt1]

hi
you could use a hyperlink.
right click the sheet name you want to go to then click hyperlink
at far left under link to, click "place in this document". a list of your
sheet should come up. click the sheet you want and type in the cell referenct
in the text box above the sheets.

regards
FSt1

 

[Remington]

Thank you. This does work, but will take some time to put together. Will be
well worth the time spent setting up in the long run. I appreciate the quick
response. Thanks.

 

[Luke M]

If you pasted the macro into the sheet module, you should be able to click on
any of the cells in range A2:A100 that contain a sheet name, and the macro
should take you there.

So, note that you will need to create a list of sheet names on your content
page, and you may want to adjust the range callout in macro, to fit your
needs.