Keyboard shortcut to switch between sheets in Excel

Solution 1:

Ctrl+PgUp to move to the left.

Ctrl+PgDn to move to the right.

Solution 2:

VBA Macros and Custom Keyboard Shortcut to Activate the First or Last Sheet

If you want an actual keyboard shortcut to jump to the first or last worksheet, put this code into a module in the "PERSONAL" workbook:

Sub ToFirstSheet()
    Sheets(1).Activate
End Sub

Sub ToLastSheet()
    Sheets(Sheets.Count).Activate
End Sub

Go to the Developer Tab > Macros. Browse to these macros (ToFirstSheet and ToLastSheet). Select one, click Options and assign a keyboard shortcut. Do the same for the other.

By saving this to the "PERSONAL" workbook it will be available in any Excel file.

Built in Keyboard Shortcuts to Activate Previous or Next Sheet

To jump one worksheet to the left or right you can use these default keyboard shortcuts:

Ctrl+PgUp

Ctrl+PgDn

VBA Macros to Activate Previous or Next Sheet

Here's how to accomplish the same using VBA try this:

Sub ToPreviousSheet()
    If ActiveSheet.Index = 1 Then
        ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Activate
    Else
        ActiveWorkbook.Worksheets(ActiveSheet.Index - 1).Activate
    End If
End Sub

Sub ToNextSheet()
    If ActiveSheet.Index = ActiveWorkbook.Worksheets.Count Then
        ActiveWorkbook.Worksheets(1).Activate
    Else
        ActiveWorkbook.Worksheets(ActiveSheet.Index + 1).Activate
    End If
End Sub

VBA Functions to Return Previous or Next Sheet

Use this function if you would rather get the previous or next worksheet object:

Function GetPreviousSheet(ByVal targetSheet As Worksheet) As Worksheet
    Dim targetBook As Workbook
    Set targetBook = targetSheet.Parent

    If targetSheet.Index = 1 Then
        Set GetPreviousSheet = targetBook.Worksheets(targetBook.Worksheets.Count)
    Else
        Set GetPreviousSheet = targetBook.Worksheets(targetSheet.Index - 1)
    End If
End Function

Function GetNextSheet(ByVal targetSheet As Worksheet) As Worksheet
    Dim targetBook As Workbook
    Set targetBook = targetSheet.Parent

    If targetSheet.Index = targetBook.Worksheets.Count Then
        Set GetNextSheet = targetBook.Worksheets(1)
    Else
        Set GetNextSheet = targetBook.Worksheets(targetSheet.Index + 1)
    End If
End Function

Use the functions like this:

Sub EXAMPLE()
    MsgBox "Previous Sheet:  " & GetPreviousSheet(ActiveSheet).Name
    MsgBox "Next Sheet:  " & GetNextSheet(ActiveSheet).Name
    GetNextSheet(ActiveSheet).Activate
End Sub

Solution 3:

You can also use accelerator keys to get to the Go dialog box. You can then type something like foo!A1 to navigate to the top left cell in the sheet named "foo". While page up and page down are generally quicker. If you have a large number (e.g., 20+) of well named sheets, this may be quicker. Go also works well if you have named tables in your sheets.

Pressing F5 normally opens the Go dialog box.