Switching between sheets - shortcut?

Stringt

New Member
Joined
Feb 18, 2002
Messages
35
I often find myself moving from one sheet in a workbook to another over and over. I wanted a shortcut that moved between the last two sheets selected.

Similar to how Alt+Tab works with windows.

Does anybody know a keyboard shortcut or if not is there a macro that could be added to perosnal.xls that would mean i would aways b able to switch between two sheets quickly.

Cheers

Tim
 

Thanksamillion

New Member
Joined
Mar 5, 2012
Messages
10
Hey shg thanks for the brilliant bit of code, it worked great! I really appreciate it. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,825
Office Version
  1. 2010
Platform
  1. Windows
You're welcome, good luck.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Maybe I'm missing the point of all this, but isn't using the sheet name tabs within the same workbook, or using the file name tabs on the task bar for different workbooks just as fast as using a keyboard shortcut?
 

guts80

New Member
Joined
Nov 8, 2012
Messages
2

ADVERTISEMENT

Re: Updating code to work with chartsheet

Shg, the code works great for worksheets...but fails for chartsheets. Since there is no sheet object (to reference either worksheet OR a chart sheet) where should the conditional decalaration of the variable go to make this code work for both types of sheets?

BTW, i'm using this code within a personal workbook (code bplaced in thisworkbook of personal workbook) instead of an add-on.

Cheers
 

guts80

New Member
Joined
Nov 8, 2012
Messages
2
Re: Updating code to work with chartsheet

Shg, the code works great for worksheets...but fails for chartsheets. Since there is no sheet object (to reference either worksheet OR a chart sheet) where should the conditional decalaration of the variable go to make this code work for both types of sheets?

BTW, i'm using this code within a personal workbook (code bplaced in thisworkbook of personal workbook) instead of an add-on.

Cheers

Whoops just figured out how to solve this problem: Just change the variables to "Object" instead of "Worksheet". The code will then work for both chartsheet and worksheet.

Cheers!
 

rhincodon

New Member
Joined
Jul 18, 2013
Messages
1
Hey shg thanks for the brilliant bit of code, it worked great! I really appreciate it. <!--?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /--><o:p></o:p>

Thank you all, it worked beautifully on my Excel 2007. :)

And for those who are still confused of this macro versus ctrl+pg up/down, the former will allow you to switch between non-adjacent tabs (eg. sheet 1 and sheet 5) while the latter is for switching between adjacent tabs only (sheet 1 and 2).
 

shimon.amar

Board Regular
Joined
Nov 20, 2012
Messages
93
Ah, the benefits of testing ....
Code:
Option Explicit
 
Public WithEvents app As Application
Dim wksOld          As Worksheet
Dim wksNew          As Worksheet
 
Private Sub Workbook_Open()
    Set app = Application
    Application.OnKey "%{CAPSLOCK}", ThisWorkbook.Name & "!Thisworkbook.LastSheet"
End Sub
 
Private Sub app_SheetActivate(ByVal Sh As Object)
    Set wksOld = wksNew
    Set wksNew = Sh
End Sub
 
Sub LastSheet()
    If Not wksOld Is Nothing Then wksOld.Select
End Sub


Can you please tell me (step by step) how and where should I implanting this kind of code? very frustrated of the lack of knowledge cause, if I understood right, this is not a regular macro to put on a module...
 

Forum statistics

Threads
1,148,179
Messages
5,745,199
Members
423,932
Latest member
pablo2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top