14
5
Does Excel 2007 have a keyboard shortcut that switchs between worksheets?
14
5
Does Excel 2007 have a keyboard shortcut that switchs between worksheets?
20
Ctrl+PgUp to move to the left.
Ctrl+PgDn to move to the right.
3
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.
To jump one worksheet to the left or right you can use these default keyboard shortcuts:
Ctrl+PgUp
Ctrl+PgDn
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
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
How can I get the next and previous sheet? I want to make a Sub ToPreviousSheet
and ToNextSheet
? – theonlygusti – 2018-04-24T10:02:19.247
Use the sheet index for this. I'll update my answer to include this. – ChrisB – 2018-04-24T16:11:57.927
1
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.
This also applies to Excel 2013 =) – Metafaniel – 2016-03-10T18:53:23.173
and on a macbook w/o pgup and pgdown? – theonlygusti – 2018-04-24T10:02:34.327
1I wish they had a better shortcut, as the
Page Up
andPage Down
are always hard to find in different laptops and not very conveniently placed on keyboard. – Cricrazy – 2018-11-08T22:38:53.573