Macro to move to next sheet

lPinkFloyd

Board Regular
Joined
Nov 22, 2003
Messages
92
I tried to record a macro to move to the next sheet in the workbook, but couldn't get it to take. I got an error 28 something about stack error. I

want to make one to move forward and another to move backward by one sheet at a time. Also the record time seened to take forever.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
To avoid run time errors when the active sheet is the last (trying to go next) or the first (trying to go the the previous), then this worked when tested. Assumes no sheets are hidden...if there are hidden sheets post back for an addended code.


Next sheet:

Sub Test1()
On Error Resume Next
Sheets(ActiveSheet.Index + 1).Activate
If err.number <> 0 Then Sheets(1).Activate
End Sub


Previous sheet:

Sub Test2()
On Error Resume Next
Sheets(ActiveSheet.Index - 1).Activate
If err.number <> 0 Then Sheets(Worksheets.Count).Activate
End Sub
 
Upvote 0
how would you get around a hidden sheet, would you unhide them all go to next then rehide

just curious

thanks

JOn
 
Upvote 0
To avoid run time errors when the active sheet is the last (trying to go next) or the first (trying to go the the previous), then this worked when tested. Assumes no sheets are hidden...if there are hidden sheets post back for an addended code.

I need this code but to let it skip hidden sheets. How do I do it?
 
Upvote 0

Forum statistics

Threads
1,203,388
Messages
6,055,126
Members
444,763
Latest member
Jaapaap

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