Moving b/w worksheets

[Norman Harker]

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]

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]

Hey! That's a cool trick!

Mike

[David McRitchie]

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 ...