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?
0
Utf
12/31/2009 11:20:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1364 Views

Similar Articles

[PageSpeed] 37

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?
0
Utf
1/1/2010 1:36:01 AM
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?
0
Utf
1/1/2010 2:04:01 AM
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?
0
Utf
1/1/2010 2:21:01 AM
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?
0
Utf
1/1/2010 5:04:02 AM
Reply:

Similar Artilces:

Password protect workbook
I want to require a password to view an Excel 2000 document. "Help" tells me to "save as" and then go to "tools" and click on "general" tab. That's where I get lost! The next instruction is to go to the "password to open" box and type in a password but I don't see that on my "general" page. Please help!! >-----Original Message----- >I want to require a password to view an Excel 2000 document. "Help" tells me to "save as" and then go to "tools" and click on "general" tab. ...

PC always opens 3 workbooks
Every time I open a spreadsheet I get three workbooks opened. One is the one I clicked on, another is called "book1"and the other is called "personal [read only]". I use a networked pc at work. How do I change the settings so only the document I clicked on is the one that opens? -- Onky Wonky ------------------------------------------------------------------------ Onky Wonky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34542 View this thread: http://www.excelforum.com/showthread.php?threadid=543084 Open excel. Make personal.xls the a...

Scheduling an update to a page
Can anyone advise what happens to the current version of a page when you create an updated version which you publish scheduled to go live at some point in the future? Does the current version become unpublished, leaving nothing but some kind of error message, or does it stay up until replaced by the new one? Obviously the second of these is what I'd like... If you are talking about pages in a publishing site of SharePoint, the pages library has versioning enabled. Users see the current major (published) version of the page when they view the page. As you modify the page yo...

Background page number
Can anyone tell me how to finally rid my spreadsheets of that annoyin background watermark page number? It shows a big over-powering "Pag 1" on the sheet. Must get rid of it. Please send some relief. I hav tried everything -- srcd ----------------------------------------------------------------------- srcdc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1554 View this thread: http://www.excelforum.com/showthread.php?threadid=27107 It appears that you are in the page break preview mode. Go to the view menu option and select 'Normal'. Th...

Quick Question!
Hi All, I have a Dialog based MFC app, I want to draw something on the dialog, so How do I call an OnDraw method here? TIA Vai2000 wrote: > Hi All, I have a Dialog based MFC app, I want to draw something on the > dialog, so How do I call an OnDraw method here? > > TIA > > The dialog receives WM_PAINT. A better and safer way is to put a CStatic control (picture control) on your dialog template, then derive a class from CStatic. Create a control member variable (use class wizard) of your class in the dialog. Paint in your CStatic WM_PAINT message handler. -- S...

Merging a shared workbook
I have 3 different sharred workbooks with one master sheet that I am trying to merge the others into. When I try to merge the workbooks I get the error that excel needs to close. Any ideas of a bug patch or fix for this problem or am I sharring incorrectly? Thanks, Corby Not enough info to determine if everything was done correctly, but if you can get that far it probably is. Closing is a part of the process, then the file should reopen automatically.|:>) "Corby" wrote: > I have 3 different sharred workbooks with one master sheet that I am trying > to merge the o...

Disable Quick Campaign
What is the best way to disable - or better still hide - the quick campaign button for certain users? It doesn't appear to be itemized in the security roles. We can disable bulk emails etc, but would prefer that the button just doesn't appear at all. When I tried exporting sitemap and removing the relevant line, the system did nasty things after I re-imported. Hi Jenny, in v3.0 the quick campaign functionality permission is grouped with activities permission. if you take away activities permission you will not be able to see the quick campaigns button. -- Manisha Powar (Mad...

Is there a quick way to count how many emails are in my .PST file(s)
Hi Is there a quick way to count how many emails are in my .PST file? (Why? Because I want to know whether GoogleDesktop has managed to spider my archive file properly...) Ship Shiperton Henethe IN OUTLOOK? - Click on the highest level folder Left-click and select properties There should be a button there called "Folder Size" Click it..... "ship" <shiphen@gmail.com> wrote in message news:1161969600.785376.108820@k70g2000cwa.googlegroups.com... > > Hi > > Is there a quick way to count how many emails are in my .PST file? > > (Why? Because ...

Recipient policy applied to a specific OU
Hi, Is there a way to apply a recipient policy to a specific OU. for example : OU abc contains users with SMTP address xxx.com and OU xyz contains users with SMTP yyyy.com. Is it possible with recipient policy to do that ? I didn't find a query to resolve my problem. Thanks ! Not according to http://support.microsoft.com/?id=296112 -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Fred" <Fred@discussions.microsoft.com> wrote in message news:1988D9E0-E8E8-4A2D-B036-2C154A867DBA@microsoft.com... > Hi, > &...

i want to open a second workbook automatically when i open the fir
both work books are in C:\ the master workbook name is test the slave workbook name is data --You can open multiple workbooks and save as workspace .xlw. Opening this workspace would open all linked workbooks. --VBA method...Set the security level to low/medium in (Tools|Macro|Security). Open master workbook. Press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(She...

How do I get real page numbers shown instead {Page }
I must have unwillingly reset the program, because instead of page numbers it gives {PAGE } at the bottom of each page. Also, instead of showing an equation it says {EMBEDDED }. hOW DO i SET IT BACK TO NORMAL DISPLAY? Alt-F9 (or, individually or for whichever ones happen to be selected, Shift-F9). On Jan 2, 5:44=A0pm, Gregory <Greg...@discussions.microsoft.com> wrote: > I must have unwillingly reset the program, because instead of page number= s > it gives {PAGE } at the bottom of each page. Also, instead of showing an > equation > it says {EMBEDDED =A0 }....

Inverted pages for use in instruction manuals
I'm looking for a template or download that I can use to create an instruction manual that has every other page inverted. For instance, the front of every page is in English and, when the manual is finished, it can be turned over and upside down to show the Spanish instructions. Any suggestions? The manuals I've seen that look like that tend to have the front half in English and the back half upside down in Spanish. Select everything on the Spanish page, group it, rotate it. -- JoAnn Paules MVP Microsoft [Publisher] "TechWriter" <TechWriter@discussions.micr...

Tab Info
How put unfo form tab (Excell sheet name) into a cell? WB has to be saved =MID(CELL("filename",A1),FIND(" ]",CELL("filename",A1))+1,32) -- Regards, Peo Sjoblom "Laura" <anonymous@discussions.microsoft.com> wrote in message news:05ef01c3dde9$2d8e6110$a501280a@phx.gbl... > How put unfo form tab (Excell sheet name) into a cell? ...

Database Location
How would I change a file location to read something like Database Location plus folder name. I need to be able to more my data base with associated folders w/o having to change codes in data base with folder location. I currently have (server) "\\homeserver\homes\user\My Documents\Pictures" ' Need to read something like "= db location & "\Pictures"" Note: I have tried this and it does not work. -- Work is sometimes hard....but someone has to do it. I think that should do what you need. http://www.blueclaw-db.com/current_path_directory.htm -- ...

Print different page setting on reports, please....help!!
Can anyone know how to make a report print different orientation. I need to print a report of two pages, the first page set as landscape and the second as portrait. I try to change the section and force that into next page but just can't find out how to make it print different page setting. Any help would be appricate.....thank you very much. Is there a reason you can't print two different reports 1 after the other? "Blackcolour" wrote: > Can anyone know how to make a report print different orientation. I need to > print a report of two pages, the first page s...

Locate Link Browser #7
I am using Windows 2000 Pro. as the OS on this computer and I have Office 2003 installed. Both products are fully updated. Several days ago I started seeing this strange behavior when I clicked on an embedded email link. When I clicked the link a small Windows Explorer box popped up with "Locate Link Browser" in the title bar. I found a Microsoft support article that gave a fix for the problem so I followed the instructions and sure enough it seemed to fix the problem. Then I suddenly was unable to open any Outlook links until I got a fix from Diane Poremsky on this forum. Aft...

IE8 Print->Print->Page Setup doesn't open dialog
I am on Windows 7 x64 with IE8 (32-bit). When I try to open the page setup dialog it does nothing. Ok, well it does the blue ring for a second or two then no dialog. I tried lots of things including rebooting and still no dialog. It works on IE8 (64-bit) just not IE8 (32-bit). Extra info: Yes, I looked around for dialog on other screen, under other windows, under my desk, etc. :-) The print preview works (except for the page setup button on it). The print works and it does print. IE8 Version: 8.0.7600.16385 Update Versions: 0 Win 7 Ultimate I ran win update and found 1 IE8 sec...

Changing 1 word to another word on a different page
I have a work sheet that if an answer in a cell on page 2 is "Yes" or "No", I need it to display the opposite in a cell on page 1 In a1 in sheet1 put =IF(Sheet2!A1="yes","no","") -- Russell Dawson Excel student "Circuitman57" wrote: > I have a work sheet that if an answer in a cell on page 2 is "Yes" or "No", > I need it to display the opposite in a cell on page 1 Missed a bit =IF(Sheet2!A1="yes","no",IF(Sheet2!A1="no","yes","")) -- ...

tab control #3
i wanted a code.. which used one main dialog and 5 other sub dialogs. the subdialog will appear with the main dialog depending on the tab control. for ex:1 on click empo we will get Main Screen + Emp details on click sal we will get Main screen + sal details Main screen and sub screen will be seperated by a single line.. Two ways of doing :- Either we can design one single dialog and put all the controls in sub screen and get them hide, disable after that depending upon tab controls we can activate the controls on respective screens 2) Main screen, child window and we have to call c...

Graphics Bleeding on Other Pages
Help! When I insert graphics to a page in my booklet it is also showing up on another page. How do I stop this from happening and 'why' is this happening? What am I doing wrong? Thanks! -- Blessed Assurance Does this only happen when you print? If you have objects in the scratch area, drag them to the top or to the bottom of your publication, then print. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Ann" <ann@mindspring.net.(donotspam)> wrote in message news:255BBF8D-F781-4DE5-9F62-083B04B0DD5C@microsoft...

Disable Customize toolber tabs
Dear All, I want to disable Customize toolbar tabs.see the scren shot i attachment. what is the code to disable the Tabs. please help me... Regards, Amar.. +------------------------------------------------------------------- |Filename: Disable Customize toolber tabs.doc |Download: http://www.excelforum.com/attachment.php?postid=3934 +------------------------------------------------------------------- -- aredd ----------------------------------------------------------------------- areddy's Profile: http://www.excelforum.com/member.php?action=getinfo&use...

To scroll down a page
Hi all, How I can make a slide by scrolling up the page. Please advice me With thanks and regards Pol If you want the slide to appear if it is coming from the bottom or the top use the wipe animation and adjust the time to your liking. Always a good idea to mention the version of PowerPoint your working with. -- Michael Koerner MS MVP - PowerPoint "pol" <pol@discussions.microsoft.com> wrote in message news:EE894FD0-EF08-4633-BF5F-4F49E5D0ECAB@microsoft.com... > Hi all, > > How I can make a slide by scrolling up the page. Please advice...

Can't print pages in proper order
Hi, [Publisher 2003] I created a four-page folded card to print 5.5" wide x 8.5" high on standard 8.5" x 11" paper. I started with a blank Publisher document. There's a photo and text on the cover and two pages of text inside the folded card. No matter how I arrange the page order, the "cover page" with the photo always prints on the wrong half of the sheet of paper, so it is positioned on the back page of the folded card, rather than the front. That is, it prints on the left, instead of the right side of the unfolded sheet. I checked a Publisher templ...

Wrong date on home page brings up 2 year old investments-can't cha
When I open Money 2007, my date showing on the left side is 2 years off-not exact. I have tried to advance it when I look at my investments page but it says I can't set a date in the future. Can this date be changed to refllect my current totals? I hate to reload everything. I reloaded a backup from a week ago but it didn't correct the problem. Please help, thanks. =?Utf-8?B?VGVk?= <Ted@discussions.microsoft.com> wrote in news:C08B8251-AC6C-42FE-8519-911864B52F27@microsoft.com: > When I open Money 2007, my date showing on the left side is 2 years > off-not exact. I ...

Workbook Growth Excessive
This workbook with a half dozen worksheets keeps my checking account info. The largest is the History sheet with 4100 rows, 9 columns, no calculation. Next is the Current sheet with 700 rows, 9 columns, numerous calculations including links to a "checks cleared" sheet . Up until a couple months ago this file increased by about 50K/mo; now it's increasing by 500K. I've made no significant increases in the rate at which I've added data. Just scrolling down in the Current sheet is slow and CPU use jumps to 50%. I get the same result even with auto-recalculation off. I&...