Shortcut to Move between Two Worksheets

For those attempting to switch between sheets via another method than Ctrl+ Pg up / Pg down [me due to my keyboard on my HP Elitebook not including a dedicated button for Pg up/down] i used Mark's code below (thanks Mark!) with a slight modification [activate rather than select in the command] given that Mark's original code was buggy in my version of excel [2016]. Here is the code, which seems to working fine but i will note that ive just installed it so have only tried it out in a few workbooks:

Sub Change_Sheets_Left()
' Keyboard Shortcut: Ctrl+k
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet > 1 Then
Sheets(CurrentSheet - 1).Activate
Else
Sheets(SheetNum).Activate
End If
End Sub

Sub Change_Sheets_Right()
' Keyboard Shortcut: Ctrl+l
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet < SheetNum Then
Sheets(CurrentSheet + 1).Activate
Else
Sheets(1).Select
End If
End Sub

@ Dan Gazit

Hi Dan,
You might want to try using a hyperlink.
Use the key combination CRTL and K to start the hyperlink wizard.

I use hyperlinks on a list of names in a weekly draw collection sheet to take me to the individual's own record payment sheet.............just wish I had discovered hyperlinks back in January 1996 when I took over the draw.

Hope this helps?

I want to use two different price lists (selling price and stock price when bought) that are on two different sheets in one workbook. I want to use the same detail list but swap between the price lists prices depending if the manager wants the list or a client without having two lists to keep updated. Is this possible?

I meant to move to another sheet (not cell):

I have on sheet 1 a list of all the sheet`s names. Each one in different cell.
How do I move to a certain sheet just by clicking on the name in sheet 1 ? (It will be, each time, different sheet)

I have on sheet 1 a list of all the sheet`s names. Each one in different sheet.
How do I move to a certain cell just by clicking on the name in sheet 1 ?

How to switch between different sheets when u have named that sheet with some different names on every sheet,because currently ctrl-pg up not working in excel 2013

if we have 50 sheets in worksheet 1,2,3,4,5,6,7,8......50.
and we have to in 2nd sheet and jump to 40 no sheet which short cut use for it without ctrl+pgup pgdn.
directly to the 2 sheet to 40th sheet

Hi Santhosh

You are going to need a sheet with the dates each person uses a particular tool. It could look something like this:

Date Employee Tools
01/2/2016 Bob Tool1
01/2/2016 Fred Tool2
01/2/2016 Ged Tool6
01/2/2016 Jon Tool7
01/2/2016 June Tool4
01/2/2016 May Tool3

Then I would create a pivot table (Insert --> Table --> Pivot Table

Place the Date Field in the Report Filter, the Employee Field in the Column Labels and the Tools in the Row Labels

You can click on the Date Filter to select the dates you want counted.

On the actual List where you make your entries You could use Data Validation to ensure that Employees are not mis-typed. You could also do the same with with the Tools column. However, Data Validation only applies to one list AFAIK. Perhaps you could merge both list elsewhere a then sort them.

If you decide to use Data Validation be sure to click the check box saying appy to all cells with same setting.

HTH

Hi, am santhosh. I am having 800 tools in sheet 1 600 tools in sheet 2 and number of person is 7 in sheet 3. I need to calculate frequency of this 7 persons that how many times they are using the tools in a month and it should be calculated in sheet 3. Please help me to solve this.

Really Nice Shortcut

Hi, I was good in excel 20 years ago, since then, I haven't work with it until now. I have forgotten so many... back to learning in Excel 2016 ;)

Thanks :)

@Matt J - you can do that. First, have you tried Ctrl-PgUp and Ctrl-PgDn to move between worksheets? It is the same as using Alt-Tab to move between applications. If you want direct access to the last sheet, you will have to create a macro. I put all my macros in one workbook and open it. Then, as I open and work in a different workbook, the macros are available. Setup a hot key for them and you have quick access and there is no need to put the macro in all the different workbooks.

I appreciate the use of macros, but that also assumes I'll always be working within the same workbook.

I would like to swap to the last active tab (worksheet?) in the same way that I can alt-tab to the last active window/application in the Windows O/S. This should be implemented in the software; users should not have to create a macro or hyperlink with every new workbook - not that you guys are responsible for this. Microsoft needs to better their software.

CTL+PgUp/PgDn doesn't work in my version of Excel so I created these Macros comparable to Windows ALT-TAB----

Sub Change_Sheets_Right()
' Keyboard Shortcut: Ctrl+Shift+X
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet < SheetNum Then
Sheets(CurrentSheet + 1).Select
Else
Sheets(1).Select
End If
End Sub

Sub Change_Sheets_Left()
' Keyboard Shortcut: Ctrl+Shift+Z
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet > 1 Then
Sheets(CurrentSheet - 1).Select
Else
Sheets(SheetNum).Select
End If
End Sub

Thanks for the tip Mike H. Appreciated very much

Can I create a custom shortcut to change CTRL+PgUp & CTRL+PgDn to ALT+Q and ALT+W, respectively?
Would make it easy to do with one hand, and places this function right next to CTRL+Tab (standard shortcut to switch windows).
Thanks in advance!

Any shortcut key that can jump from sheet to another sheet that have the source of formula.
eg: I've ='Sheet2'!S69 in my Sheet1 cell, I want a shortcut key that can jump to Sheet2 the particular cell. Thks. pls advise

Thank you very much, valuable information

Thank You soo Much Sir

I have also discovered in Excel 97 that CRTL/TAB and CTRL/TAB/SHIFT combinations allow me to move back and forth between open work books.

Thank You very much Mike.

Indeed a very very helpful information.

Cheers

Roger:

There is, at least in Excel 2010, a built-in way to select from a list of worksheets. Just right-click on the arrows in the lower LH corner of the Excel window (to the left of the worksheet tabs.) A pickable list of worksheets will be generated.

Hi Mike H,

I also use hyperlinks to switch between 2 work sheets in separate Excel 97 files.

After using one of the hyperlinks I have found that I can quickly switch back and forth between sheet using the ALT/Left or Right Arrow.

Which saves having to find the mouse, mouse arrow and then click on a hyperlink.

hi
Mike

FIRST of all thanks a lot,
for solution on moving btwn sheet 1 to sheet 2

This was 1 needed.

Thanks AND keep ur work going.

I have a excel file that has a main list sheet and then a series of detailed sheets. I want the main list next to the sheet that I'm working on. So I wrote a macro that makes a list of all the worksheets, puts it into a popup menu from which I can select and then moves the main list sheet in front of the worksheet that I select. And since I have a variety of different files some of which have unique sheets, it always displays the sheets of the one that I'm working on. It has saved me a lot of work in moving that main list around.

You can also open a new window of the same book and open the other sheet.
Move between windows of the same book using Alt+Tab.

thanks , it was helpful

Another easy method is you could always create a hyperlink via (Ctrl + K) Insert Hyperlink and followed by -select "Place in this document" then edit "Text to display" to some appropriate word or wording then type in / change cell address (default A1)and then select sheet to jump to. Then one to get back.

This way you have a simple one left click action to jump between sheets and back again and even to a specific cell / area.

Have used this in a menu sheet to jump to other sheets in same file for ease of navigation for other users I have sent the file to.

Hope you find this of interest and help.

Mike H

If the non-adjacent worksheets, just repeat the Ctrl-PgUp or Ctrl-PgDn and you will move to the next worksheet. You don't need to use a macro.