Create Keyboard Shortcuts to Select the First or Last Sheet in Excel

Bottom line: Learn how to create a keyboard shortcut to select the first or last sheet in Excel.  There is no dedicated shortcut for this, so this solution uses a macro.

Skill level: Intermediate

Custom Keyboard Shortcuts to Select the First or Last Sheet in Excel

Macros to Select the First or Last Sheet

Unfortunately, there are no dedicated keyboard shortcuts to select the first or last sheets in Excel.  However, we can use a simple set of macros to do this, and assign keyboard shortcuts to them.

Here are the macros to select the first or last sheet tab in the active workbook.

Macro to Select the First Sheet Tab

Sub Select_First_Sheet()
'Select first visible sheet in the workbook

Dim i As Long

  With ActiveWorkbook
    'Loop through all sheets in the workbook
    For i = 1 To .Sheets.Count
      'Check if the sheet is visible
      If .Sheets(i).Visible = xlSheetVisible Then
        'Activate/select the sheet and exit the loop
        .Sheets(i).Activate
        Exit For
      End If
    Next i
  End With

End Sub

Macro to Select the Last Sheet Tab

Sub Select_Last_Sheet()
'Select last visible sheet in the workbook

Dim i As Long

  With ActiveWorkbook
    'Loop through all sheets starting from the last sheet
    For i = .Sheets.Count To 1 Step -1
      'Check if the sheet is visible
      If .Sheets(i).Visible = xlSheetVisible Then
        'Activate/select the sheet and exit the loop
        .Sheets(i).Activate
        Exit For
      End If
    Next i
  End With

End Sub

These macros can be added to your Personal Macro Workbook (PMW).  Checkout my 4-part video series on How to Create Your Personal Macro Workbook if you don't have one setup yet.

I also have a post and video on how to copy VBA code to other workbooks.

The macros use a For Next Loop to loop through all sheets and select (activate) the first or last visible sheet.  Checkout my post on The For Next and For Each Loops Explained for VBA to learn more.

Since the macros only activate sheets, we do NOT lose the undo history when the macro runs.

Assign the Keyboard Shortcuts to the Macros

Once the macros are in your PMW, you can assign keyboard shortcuts to them.  There are two ways to do this, and I have an entire post and video on 2 Ways to Assign Keyboard Shortcuts to Macros.  Here is an overview of the different techniques.

#1 Macro Options Window

The first way is to assign the shortcut key in the Macro Options window. In the following screenshot I assign Ctrl+Shift+A to the Select_First_Sheet macro. And Ctrl+Shift+S to the Select_Last_Sheet macro.

Assign Keyboard Shortcut in Macro Options Window

#2 Application.OnKey Method

The other way is to use the Application.OnKey method. Here is the VBA code for those same shortcut keys.

Sub CreateShortcuts()

  Application.OnKey "+^{a}", "Select_First_Sheet"
  Application.OnKey "+^{s}", "Select_Last_Sheet"

End Sub

I explain how to fully automate this, and the pros & cons to each technique in my post 2 Ways to Assign Keyboard Shortcuts to Macros.

Make sure to save your PMW after the setup is complete.  It's important to note that you only have to do set this up one time.  Every time you open Excel in the future you will be able to use the keyboard shortcuts on any open file.

Here is a screencast of the shortcut keys in action.

Custom Keyboard Shortcuts to Select the First or Last Sheet in Excel

Conclusion

These shortcuts should definitely save you time when navigating large workbooks.  Checkout my post on 7 Shortcuts for Working with Worksheet Tabs in Excel to learn other time-saving tips.  I also explain how to select the first and last sheets with the mouse in that article.

Please leave a comment below with any questions or suggestions.  Thank you! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 0 comments

Leave a Reply: