Next sheet from end to beginning ?

magnum0357

New Member
Joined
Feb 8, 2009
Messages
29
I have the following macro........

Dim i
If ActiveSheet.Index = Sheets.Count Then Exit Sub
For i = ActiveSheet.Index + 1 To Sheets.Count
If Sheets(i).Visible = xlSheetVisible Then
Sheets(i).Select
Exit For
End If
Next

This macro starts at which ever sheet I am on and goes from front to end say 1 to 16.....my question is how do reverse the code to go to the next unhidden sheet from 16 to 1 ?

any help will be greatly appreciated........

magnum
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Try this

Code:
Sub test()

For i = Sheets.Count To 1 Step -1
MsgBox i ' your code here
Next i

End Sub
 
Upvote 0
Not working.........but by misunderstanding.......
the code that I have allows me to jump from one sheet to another......skipping the hidden sheets......if I am on sheet3 and sheet 4 is hidden my macro jumps to sheet 5 or the next unhidden sheet....but it only works from sheet 1 going in the direction of sheet 16....what I am looking for is to go backwards say from sheet 16 to sheet 1 ? ?
 
Upvote 0
doing this off heart, so may hickup

Sub reverseme()

Dim i
i = 1
r = 1


If ActiveSheet.Index = Sheets.Count Then Exit Sub
For r = Sheets.Count To i = ActiveSheet.Index + 1 Step -1
If Sheets(r).Visible = xlSheetVisible Then
Sheets(r).Select
r = r + 1
Exit For
End If
Next r



End Sub
 
Upvote 0
if i replace the +1 to -1 in my original macro it does allow me to switch from sheet to sheet going backwards as long as there are no hidden sheets, but when i reach a hidden sheet the macro stops.........? ? ?
 
Upvote 0
Your code "stops" if it finds a sheet in the sheet count that is visible.

What should happen?

try

Code:
Sub reverseme()

Dim i
i = 1
r = 1

For r = Sheets.Count To 1 Step -1
If Sheets(r).Visible = xlSheetVisible Then
Sheets(r).Select
r = r + 1
Exit For
End If
Next r

End Sub
 
Upvote 0
it stops upon finding a "hidden" sheet..........if I am going backwards and I am on sheet 6, when I run the macro and sheet5 is visible it sends me to sheet5..BUT if sheet 5 is hidden it will not go on to sheet 4 like it should......
 
Upvote 0
Ok - i get what you mean now :)

Sorry, bit thick on sunday night.
I tested this version, and it skips hidden and goes to previous.

Code:
Sub reverseme()
 
Dim i As Long
    
    For i = 1 To Sheets.Count
        If Sheets(i).Name = ActiveSheet.Name Then
            
            Exit For
        End If
    Next



For r = i - 1 To 1 Step -1
If Sheets(r).Visible = xlSheetVisible Then
Sheets(r).Select

Exit For
End If
Next r

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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