Macro to navigate between sheets

[Jonsson]

Hi all,

I need help to create 2 macro. One that choose the sheet before th
active sheet, and one that choose the sheet after the active sheet
nomatter whats the name of the sheet.
Just navigate to the sheet left of the active sheet, and right of th
active sheet.


Any help is appreciated!!

//Thoma

 

[Paul B]

Thomas, try this, you can also use Ctrl+ page up and page down on the key
board

Sub goto_next_sheet()
On Error GoTo E
Sheets(ActiveSheet.Index + 1).Select
Exit Sub
E: MsgBox "This Is The Last Sheet"
End Sub

Sub goto_previous_sheet()
On Error GoTo E
Sheets(ActiveSheet.Index - 1).Select
Exit Sub
E: MsgBox "This Is The First Sheet"
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Bob Phillips]

Hi Jonsson,

here are 2 macros for you

Sub Forward()
Dim idx As Long
idx = ActiveSheet.Index
If idx < ActiveSheet.Parent.Sheets.Count Then
Sheets(idx + 1).Activate
End If
End Sub

Sub Back()
Dim idx As Long
idx = ActiveSheet.Index
If idx > 1 Then
Sheets(idx - 1).Activate
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

If you aren't going to use the built in shortcut keys, it is
Easier to use the built in methods for this

Sub Moveright()
On Error Resume Next
activesheet.Next.Activate
End Sub

sub MoveLeft()
on Error Resume Next
activesheet.Previous.Activate
End Sub

 

[Jonsson]

Hi, Bob, Tom and Paul!

Thanks for your reply!!

They all works as I wanted!

//Thoma