Moving b/w worksheets

65 views
Skip to first unread message

Norman Harker

unread,
Nov 18, 2002, 4:10:10 PM11/18/02
to
Hi Sherm!

One of the less well known of the techniques of workbook navigation is:

Right click on any of the arrows that are immediately to the left of the
worksheet tabs.

This brings up a list of the worksheets including "More sheets" if you have
more than (I think) 17 sheets. In that case you get a pretty little Activate
dialog that allows you to pan down to select the sheet you want.

In classes of Intermediate / Advanced Excel users, very few know of the
existence of this navigation method. One of my colleagues even bought me a
drink for showing him it because he was always using a workbook with one
sheet for each of his class of over 100 students!

HTH
--
Norman Harker
Sydney, Australia
njha...@optusnet.com.au
"Sherm" <sherma...@earthlink.net> wrote in message
news:716c01c28f40$24a4e360$89f82ecf@TK2MSFTNGXA01...
> Is there a faster way of moving between worksheets than
> using the arrow system...is there a "Find" or other
> system? I have 200 alphabetized worksheets in my workbook
> and am looking for a faster way to move between them.
>
> Thanks!


Tom Ogilvy

unread,
Nov 18, 2002, 3:59:33 PM11/18/02
to
If you right click on the "VCR" controls in the lower right corner, you can
select from a list - but I am not sure what 200 sheet names would look like.

You can hit in F5 and type in something like

Sheet50!A1

then hit enter.

Regards,
Tom Ogilvy

Michael J. Malinsky, CPA

unread,
Nov 18, 2002, 4:21:18 PM11/18/02
to
Hey! That's a cool trick!

Mike

David McRitchie

unread,
Nov 20, 2002, 8:41:29 AM11/20/02
to
Hi Sherm,
Navigation between worksheets.

So far you have from Jason Morin, Norman Harker
-- move to next or previous sheet (ctrl+page down/up)
more on shortcut keys on my shortx2k.htm

and from Tom Ogilvy you have:
-- You can hit in F5 (GoTo) and type in something like -- Sheet50!A1

-- Right click on the "VCR" controls in the lower right corner (sheet
navigation arrows), you can select from a list

which you can make an improvement on with a macro.
Using the sheet navigation arrows requires another click on "More Sheets",
but you can go directly to the Activate Sheet dialog with the following macro
which you can assign to a toolbar button or to a shortcut key combination.
http://www.mvps.org/dmcritchie/excel/dialog.htm

Sub SheetList_CP()
'Chip Pearson, 2002-10-29, misc.
On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
End Sub

Another choice would be to use hyperlinks and go directly to the
sheetname in a hyperlink.
For example you can Build a Table of Contents with hyperlinks.
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
The advantage of a hyperlink is that you can use the blue
back and forward web navigations buttons, or the shortcuts
for back (Alt+LtArrow) and forward (Alt+RtArrow).

Along the lines of having a hyperlink would be an event macro to
double click on a cell with the sheetname and have the macro
take you to the sheet -- without using a hyperlink on the sheet.
See GoToSheet macro in
http://www.mvps.org/dmcritchie/excel/code/gotostuff.txt

Something similar as an Event macro that you double click on a
cell that shows the sheetname. THe Event macro activates from the
sheet it is installed in. http://www.mvps.org/dmcritchie/excel/event.htm
You will not get the Back and Forward advantage of a hyperlink.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Worksheets(Trim(Target.Value)).Activate
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Sherm" <sherma...@earthlink.net> wrote ...

Reply all
Reply to author
Forward
0 new messages