#NiftyKeyboardShortcuts: Jump between tabs (Ctrl + PgUp / PgDn)

One reason I’ve come across people wanting to learn and use keyboard shortcuts is where their dexterity has been limited by injuries they’ve previously had. One lady I met had lost fingertips from an accident and found controlling the mouse particularly tricky.

Others have come unstuck where the mouse is very sensitive, something that can often be the case with anyone who uses the trackpad on a modern laptop. I'll mention no names, Dad.

Using keystrokes can help mitigate some of these physical issues, especially where the icon requiring a mouse click is smaller than average. One case this occurs is with scroll bars, and the arrows that indicate scrolling between different Excel worksheets is one frequent example.

To activate the next worksheet, simply use Ctrl + Page Down; to switch back, use Ctrl + Page Up. Simple, and quick – and you’re also in position to release Ctrl and press Page Down or Page Up again if you need to whizz around the sheet – or Ctrl + Home to head back to cell A1. (The exception to this is if you have Freeze Panes activated, in which case Ctrl + Home takes you back to the upper left cell where the freeze starts.)

If you have loads of sheets, Ctrl + PgDn / PgUp can still seem a little tedious. You can bring up a list of all sheets to navigate to by right-clicking the workbook tabs command bar:

You can use Page Up and Page Down within this list, too. But this is cheating: we had to use the mouse. Is there anyway to jump to a non-consecutive sheet?

One way is to use the Go To option covered in a previous post: press F5, and type in the sheet name, followed by an exclamation mark (!) and a cell reference. Hit Enter and you'll jump straight to the sheet that you want.

See the post on Go To / Go To Special for further information on this kind of navigation.

This does mean, however, that you need to know the exact sheet name. So another way to get there is a tiny bit of VBA code…

---

For VBA users

Open the Editor using Alt + F11. Insert a new module using Alt + I + M, and either type or copy and paste in the following:

Sub ShowAllSheets()

Application.CommandBars("Workbook tabs").ShowPopup

End Sub

Executing this brings up the sheet list as above, and you can make your selection from there without having to remember all the sheet names.

To run the code, you can use the shortcut to open the macros dialogue box (Alt + F8), select the procedure and then run it using Alt + R.

It does mean your workbook will need to be saved as a macro-enabled file. The alternative is adding this macro to your Personal Macro Workbook and putting it on your Quick Access Toolbar. If you’ve read this far, you probably know how to do that already. If you don’t, I’ll cover it in another post!

#navigation #worksheets