Keyboard shortcut for switching through worksheets

**sinke** · 02-05-2010, 11:36 AM

Hello!

I need keyboard shortcut for switching through worksheets.

For now, I have this VBA macro and shortcut Ctrl+R:

Sub next_sheet()
'
' next_sheet Macro
'
' Keyboard Shortcut: Ctrl+r
'
ActiveSheet.Next.Select

End Sub

But, there is a problem: when i am on the last sheet, and want to go to the first, there is Error.

Any suggestions?

Thanks in advance.

Sinke

**romperstomper** · 02-05-2010, 11:39 AM

You can actually use Ctrl+Page Down or Ctrl+PageUp for that (it has the same limitation), or just use the sheet navigation buttons in the bottom left corner?

**DonkeyOte** · 02-05-2010, 11:40 AM

sinke, Welcome to the Board,

Your post does not comply with Rule 3 of our Forum RULES.
Use code tags around code.
Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.

For more information about these and other tags, found here

**sinke** · 02-05-2010, 02:38 PM

@DonkeyOte
i will keep that in mind :-)

@romperstomper
Ctrl+Page Down or Ctrl+PageUp are very non-ergonomic (or maybe i'm just being lazy) .
My combination Ctrl+R is much better :-))
Any other advice??

**DonkeyOte** · 02-05-2010, 02:40 PM

sinke, my post was not a "suggestion".

**romperstomper** · 02-05-2010, 09:18 PM

You obviously have a different keyboard than mine...

You would need a check to see if activesheet.Index is equal to either 1 or Sheets.Count (depending on which way you are going) and then select the relevant sheet rather than the previous/next.

**martindwilson** · 02-09-2010, 06:12 PM

if you must then you could just use on error

Sub next_sheet()
'
' next_sheet Macro
'
' Keyboard Shortcut: Ctrl+r
On Error GoTo firstsheet
ActiveSheet.Next.Select
Exit Sub
firstsheet:
Sheets(1).Select

End Sub

**sinke** · 02-19-2010, 03:47 AM

martindwilson , thanx for helping. your code for Next_sheet works fine.

For Previous_sheet i use:

Sub previous_sheet()
'
' previous_sheet Macro
'
' Keyboard Shortcut: Ctrl+e
'
    On Error GoTo lastsheet
ActiveSheet.Previous.Select
Exit Sub
lastsheet:
Sheets(3).Select
End Sub

I don't konw how to define "last sheet", so it jumps to 3rd sheet.
This is not a perfect solution , but for me is good enough. My problem is solved.