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?

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Remington" wrote:

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?

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Remington" wrote:

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?

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" wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Remington" wrote:

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?

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.

"FSt1" wrote:

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" wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Remington" wrote:

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?

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Remington" wrote:

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?

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Remington" wrote:

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?