Quickly locate a specific page/tab in workbook

[touchstone]

I have a workbook with over 1000 pages/tabs. Is there a command to quickly
access a specific page/tab without scrolling through the entire series to get
to the one I want?

 

[Mike Rogers]

One way:

In pre xl 2007 ( might be the same in xl2007 I just don't know) Right click
on the arrows in the lower left corner of your worksheet, (to the left of the
tabs) and you will get a list you can scroll thru to find the worksheet you
need to go to.

Mike Rogers

 

[JLatham]

It still works that way in 2007, Mike.

touchstone, here's another option that, in effect, takes the long list you'd
see by right-clicking the navigation buttons at the lower left of the
workbook window and puts it on a worksheet and allows you to just click an
entry and go right to that sheet.

Add one more sheet at the very front of your workbook. Right-click that
sheet's name tab and choose [View Code] and copy the code below and paste it
into the module presented to you and then close the VB Editor window.

Each time you choose that new 'table of contents' sheet, the list of sheets
in the workbook will be rebuilt in column A. Then when you click any of the
cells that holds a sheet name, you'll jump to that sheet. You can get back
to the table of contents sheet using the 'go to first' button in that group
of navigation buttons at the lower left of the workbook window.

Private Sub Worksheet_Activate()
Dim anyWS As Worksheet
Dim rp As Long
On Error GoTo ExitActivate
Application.ScreenUpdating = False
Cells.Clear
Application.EnableEvents = False
For Each anyWS In ThisWorkbook.Worksheets
rp = rp + 1
Range("A" & rp) = anyWS.Name
Next
ExitActivate:
If Err <> 0 Then
Err.Clear
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 And _
Target.Column = 1 And _
Not IsEmpty(Target) Then
On Error Resume Next
ThisWorkbook.Worksheets(Target.Value).Activate
End If
If Err <> 0 Then
Err.Clear
End If

 

[Mike Rogers]

Thaks J

Mike Rogers

It still works that way in 2007, Mike.

touchstone, here's another option that, in effect, takes the long list you'd
see by right-clicking the navigation buttons at the lower left of the
workbook window and puts it on a worksheet and allows you to just click an
entry and go right to that sheet.

Add one more sheet at the very front of your workbook. Right-click that
sheet's name tab and choose [View Code] and copy the code below and paste it
into the module presented to you and then close the VB Editor window.

Each time you choose that new 'table of contents' sheet, the list of sheets
in the workbook will be rebuilt in column A. Then when you click any of the
cells that holds a sheet name, you'll jump to that sheet. You can get back
to the table of contents sheet using the 'go to first' button in that group
of navigation buttons at the lower left of the workbook window.

Private Sub Worksheet_Activate()
Dim anyWS As Worksheet
Dim rp As Long
On Error GoTo ExitActivate
Application.ScreenUpdating = False
Cells.Clear
Application.EnableEvents = False
For Each anyWS In ThisWorkbook.Worksheets
rp = rp + 1
Range("A" & rp) = anyWS.Name
Next
ExitActivate:
If Err <> 0 Then
Err.Clear
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 And _
Target.Column = 1 And _
Not IsEmpty(Target) Then
On Error Resume Next
ThisWorkbook.Worksheets(Target.Value).Activate
End If
If Err <> 0 Then
Err.Clear
End If

One way:

In pre xl 2007 ( might be the same in xl2007 I just don't know) Right click
on the arrows in the lower left corner of your worksheet, (to the left of the
tabs) and you will get a list you can scroll thru to find the worksheet you
need to go to.

Mike Rogers

 

[JLatham]

You're welcome. I don't know of any way that touchstone is going to get away
from scrolling through some list - you're not going to display 1000 of much
of anything on a single screen. My code just makes the list you get from the
nav buttons a little bigger, so instead of scrolling through the tiny-font
list, you scroll through a larger-font list on a worksheet.

I even thought of setting something up with an auto-complete type function
so a person could type in a sheet name, have it auto-complete then go to that
sheet with a [Go to] button. But unless the naming convention for the sheets
is pretty simple and consistent, who's going to remember the name of 1000 or
more sheets in a workbook?!

Thaks J

Mike Rogers

It still works that way in 2007, Mike.

touchstone, here's another option that, in effect, takes the long list you'd
see by right-clicking the navigation buttons at the lower left of the
workbook window and puts it on a worksheet and allows you to just click an
entry and go right to that sheet.

Add one more sheet at the very front of your workbook. Right-click that
sheet's name tab and choose [View Code] and copy the code below and paste it
into the module presented to you and then close the VB Editor window.

Each time you choose that new 'table of contents' sheet, the list of sheets
in the workbook will be rebuilt in column A. Then when you click any of the
cells that holds a sheet name, you'll jump to that sheet. You can get back
to the table of contents sheet using the 'go to first' button in that group
of navigation buttons at the lower left of the workbook window.

Private Sub Worksheet_Activate()
Dim anyWS As Worksheet
Dim rp As Long
On Error GoTo ExitActivate
Application.ScreenUpdating = False
Cells.Clear
Application.EnableEvents = False
For Each anyWS In ThisWorkbook.Worksheets
rp = rp + 1
Range("A" & rp) = anyWS.Name
Next
ExitActivate:
If Err <> 0 Then
Err.Clear
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 And _
Target.Column = 1 And _
Not IsEmpty(Target) Then
On Error Resume Next
ThisWorkbook.Worksheets(Target.Value).Activate
End If
If Err <> 0 Then
Err.Clear
End If

One way:

In pre xl 2007 ( might be the same in xl2007 I just don't know) Right click
on the arrows in the lower left corner of your worksheet, (to the left of the
tabs) and you will get a list you can scroll thru to find the worksheet you
need to go to.

Mike Rogers

:

I have a workbook with over 1000 pages/tabs. Is there a command to quickly
access a specific page/tab without scrolling through the entire series to get
to the one I want?