Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quickly locate a specific page/tab in workbook
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quickly locate a specific page/tab in workbook
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 "touchstone" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quickly locate a specific page/tab in workbook
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 On Error GoTo 0 End Sub "Mike Rogers" wrote: 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 "touchstone" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quickly locate a specific page/tab in workbook
Thaks J
Mike Rogers "JLatham" wrote: 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 On Error GoTo 0 End Sub "Mike Rogers" wrote: 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 "touchstone" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quickly locate a specific page/tab in workbook
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?! "Mike Rogers" wrote: Thaks J Mike Rogers "JLatham" wrote: 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 On Error GoTo 0 End Sub "Mike Rogers" wrote: 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 "touchstone" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Muti-page workbook highlight specific dates that occur throughout | Excel Worksheet Functions | |||
Linking to a specific page in an excel workbook | Excel Discussion (Misc queries) | |||
Locate a specific row or column | New Users to Excel | |||
External hyperlink to a specific page in a workbook | Excel Worksheet Functions | |||
Locate/goto a specific worksheet quickly | Excel Worksheet Functions |