next sheet macro

gaur

New Member
Joined
Mar 19, 2002
Messages
1
... To begin with.. I am a total beginner.

I want to make two macros that I can assign to buttons. One should activate the next sheet in the workbook and the other button should activate the previous sheet.

:)

thanks
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi Gaur,

The following will move to the next or previous sheets in the workbook. This replicates clicking on the tabs for the sheet.

---begin VBA---
Sub next_sheet()
Dim wkshtcount As Integer
wkshtcount = ThisWorkbook.Sheets.Count
If ActiveSheet.Index <> wkshtcount Then
Sheets(ActiveSheet.Index + 1).Activate
End If
End Sub

Sub previous_sheet()
If ActiveSheet.Index <> 1 Then
Sheets(ActiveSheet.Index - 1).Activate
End If
End Sub
---end VBA---

Regards,
Jay
 
Upvote 0
Same thing only different:

Code:
sub pre()
ActiveSheet.Previous.Select
end sub

Code:
sub nxt()
ActiveSheet.Next.Select
end sub
 
Upvote 0
Hi Gaur

Just a quick ammedment to Nates

Sub pre()
On Error Resume Next
ActiveSheet.Previous.Select
On Error GoTo 0
End Sub

Sub Nxt()
On Error Resume Next
ActiveSheet.next.Select
On Error GoTo 0
End Sub


Just to stop any Run-time errors if you are on the last or first sheet.
 
Upvote 0
Right-o Dave, nice catch, and I apologize for the sloppy code. Might want to have the end-user catch some grief as below.

Code:
Sub pre() 
On Error goto errorhandler
ActiveSheet.Previous.Select 
exit sub
errorhandler:
msgbox "Yer already on the first worksheet!"
End Sub

Code:
Sub Nxt() 
On Error goto errorhandler
ActiveSheet.next.Select 
exit sub
errorhandler:
msgbox "Yer already on the last worksheet!"
End Sub

Incidentally, in all seriousness, nice site Dave.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-20 19:42
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top