shortcut to toggle between 2 worksheets

[GabbyU]

I have an xls with many worksheets. Oftentimes I need to work between 2
worksheets only, say Sheet G and Sheet B2. Is there a keyboard shortcut that
will allow me to toggle between 2 non-adjacent sheets. I cannot hide the
sheets in-between because I also need to refer to them from time-to-time.
Something similar to an Alt-Tab in Windows.

TIA

 

[Earl Kiosterud]

Gabby,

You can use Ctrl-PageUP and Down to move between adjacent worksheets. You
could move the two worksheets so they're adjacent, at least temporarily. Or
if the sheets are not adjacent and must remain so, you could record a macro
that selects a sheet, assign it to a keyboard shortcut, like Ctrl-Shift-1
and 2. Do that with Tools - Macro - Macros - Options.

I wonder if you find a sheet named like a cell (B2) confusing at times.

 

[GabbyU]

Earl,

Many thanks for your help. My worksheets do have "logical" names. My
examples were just to indicate that they could be many worksheets apart. As
changing my worksheets positions is not an option, I have to do it the
"macro" way per your suggestion. As I've never done a macro myself (seen it
explained a few times), it'll be a learning experience.

Thanks...Gabby

 

[Bob Flanagan]

Create a range name on each sheet and location that you may want to quickly
go to,. Then just press F5 and select the destination. The also works if
you are editing a formula and need to jump somewhere. Be careful not to use
a cell that you would use in a formula, to avoid having the range name used
in the formula. As needed, delete and recreate the range name if the target
location shifts.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Earl Kiosterud]

Gabby,

Start the macro recorder (Tools - Macro - Record new macro).
Give it a name, like "SheetReceiptsSelect." Put in the shortcut key. OK.
It's recording now.
Select the sheet (even if it's currently selected - click the tab anyway).
Stop the recorder (Stop button on macro toolbar, or Tools - Macro - Stop.
Repeat for other sheets.
Try the shortcuts.

To change the shortcuts, Tools - Macro - Macros - Options.

 

[GabbyU]

Hi Earl and Bob,

Thanks for your inputs. I have struggled with my first macro (Toggle ala
Alt-Tab) and am very proud of it. It works too!! Not sure if that's the
correct way of formatting the If-Then-Else-Endif construct. All I have to do
is edit the correct names for Sheet1/2. These will be changing as I would be
working with different pairs of worksheets from time to time.

I might also explore Bob's idea of using ranges, rather than Sheets; as that
could save me even more time since I don't have to scroll down on the big
worksheets.

Sub Macro3()

Dim Sheet1 As String
Dim Sheet2 As String

Sheet1 = "Sheet1"
Sheet2 = "Sheet2"

If (ActiveSheet.Name <> Sheet1) Then
Sheets(Sheet1).Select
Else:
Sheets(Sheet2).Select
End If
End Sub

Again, many thanks...Gabby

 

[Earl Kiosterud]

Gabby,

I'm not sure what you mean by Alt-Tab. That switches windows.

Your If construct is fine. Now assign Macro3 to a keyboard shortcut key
(Tools - Macro - select your macro - Options)
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Hi Earl and Bob,

Thanks for your inputs. I have struggled with my first macro (Toggle ala
Alt-Tab) and am very proud of it. It works too!! Not sure if that's the
correct way of formatting the If-Then-Else-Endif construct. All I have to do
is edit the correct names for Sheet1/2. These will be changing as I would be
working with different pairs of worksheets from time to time.

I might also explore Bob's idea of using ranges, rather than Sheets; as that
could save me even more time since I don't have to scroll down on the big
worksheets.

Sub Macro3()

Dim Sheet1 As String
Dim Sheet2 As String

Sheet1 = "Sheet1"
Sheet2 = "Sheet2"

If (ActiveSheet.Name <> Sheet1) Then
Sheets(Sheet1).Select
Else:
Sheets(Sheet2).Select
End If
End Sub

Again, many thanks...Gabby

 

[GabbyU]

Earl,

I have assigned it a shortcut key of Ctrl-Z, and it works like a charm. What
I meant is that this shortcut key now works with my worksheets the way
Alt-Tab works for Windows tasks.

Many thanks for your help,
Gabby

 

[Earl Kiosterud]

Gabby,

NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO!

You can't use Ctrl-Z!!! You have to leave that for Undo. Undo that
assignment immediately. My life would be meaningless without Ctrl-Z. How
about Ctrl-Shift-z?

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Earl,

I have assigned it a shortcut key of Ctrl-Z, and it works like a charm. What
I meant is that this shortcut key now works with my worksheets the way
Alt-Tab works for Windows tasks.

Many thanks for your help,
Gabby

 

[GabbyU]

Earl,

Did as you suggested...just wasn't pretty obvious how to insert the "shift"
part until I did it.

Thanks...Gabby

Gabby,

NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO!

You can't use Ctrl-Z!!! You have to leave that for Undo. Undo that
assignment immediately. My life would be meaningless without Ctrl-Z. How
about Ctrl-Shift-z?

 

[David McRitchie]

Hi Gabby, (sent to microsoft.public.excel with email copy)

If you goto other sheet with a hyperlink you can use the
web Back (Alt+ArrowRt) and Forward (Alt+ArrowLt) and
if you like you could install the turquoise colored arrows
Tools, Customize, Commands (tab), Web (left side), arrows on right

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#sheet5", "sheet5")
the second would not pick up a sheetname change in any
manner. You might want to enter/use object hyperlink instead (ctrl+k)

Another way would be to install an Event macro such
as doubleclick or rightclick to go to the page named
in the macro, or to go to the name of sheet listed in a
cell. Because you got there with a hyperlink you can
return with the BACK button. Install by rightclick on
sheetname, view code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
Cancel = True 'Get out of edit mode
ActiveWorkbook.FollowHyperlink ("#sheet_1920!C5")
End Sub

You can adapt the above to use rightclick instead or
to use the activecell for the sheetname.

More information on Event macros in
http://www.mvps.org/dmcritchie/excel/event.htm

More information on Hyperlinks in from one worksheet to another
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlinks

Email copy was included because your post was a week ago,
if you have more related questions or feedback would prefer you
to stick to this thread in the newsgroup.

Something similar to Alt Tabs in Windows, which doesn't sound very
useful, would be to Right-click on the navigation arrows to the
left of the worksheet tabs to bring up a list of sheetnames in
the same order that you have them. To make it more useful
you could sort the sheetnames.
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm#sortallsheets
I say not very useful because you wanted to go between two
specific worksheets, and I think the other choices are a better
match to what you want to do. More information at
http://www.mvps.org/dmcritchie/excel/sheets.htm#navigation

 

[GabbyU]

Hi David,

Thanks for your suggestions. I can see now there are so many ways to do what
I want. I have to study the HYPERLINK function a bit more, I have filed away
your reply for future reference. Also, I have been perusing your web site
for all the excel VBA learning tools. Many thanks for your great help to the
community.

Regards...Gabby

Hi Gabby, (sent to microsoft.public.excel with email copy)

If you goto other sheet with a hyperlink you can use the
web Back (Alt+ArrowRt) and Forward (Alt+ArrowLt) and
if you like you could install the turquoise colored arrows
Tools, Customize, Commands (tab), Web (left side), arrows on right

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#sheet5", "sheet5")
the second would not pick up a sheetname change in any
manner. You might want to enter/use object hyperlink instead (ctrl+k)

Another way would be to install an Event macro such
as doubleclick or rightclick to go to the page named
in the macro, or to go to the name of sheet listed in a
cell. Because you got there with a hyperlink you can
return with the BACK button. Install by rightclick on
sheetname, view code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
Cancel = True 'Get out of edit mode
ActiveWorkbook.FollowHyperlink ("#sheet_1920!C5")
End Sub

You can adapt the above to use rightclick instead or
to use the activecell for the sheetname.

More information on Event macros in
http://www.mvps.org/dmcritchie/excel/event.htm

More information on Hyperlinks in from one worksheet to another
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlinks

Email copy was included because your post was a week ago,
if you have more related questions or feedback would prefer you
to stick to this thread in the newsgroup.

Something similar to Alt Tabs in Windows, which doesn't sound very
useful, would be to Right-click on the navigation arrows to the
left of the worksheet tabs to bring up a list of sheetnames in
the same order that you have them. To make it more useful
you could sort the sheetnames.
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm#sortallsheets
I say not very useful because you wanted to go between two
specific worksheets, and I think the other choices are a better
match to what you want to do. More information at
http://www.mvps.org/dmcritchie/excel/sheets.htm#navigation
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I have an xls with many worksheets. Often times I need to work between 2
worksheets only, say Sheet G and Sheet B2. Is there a keyboard shortcut that
will allow me to toggle between 2 non-adjacent sheets. I cannot hide the
sheets in-between because I also need to refer to them from time-to-time.
Something similar to an Alt-Tab in Windows.