Moving to a previous sheet macro

[Balrajss0121]

Hi, I have a macro that enables me to move to the previous sheet. However when back at the start I would want it to loop back to the last sheet in the workbook. The current code is displayed below and it checks for any hidden sheets and it misses out the first sheet which is the home sheet. Any help would be appreciated in solving this

Sub PreviousSheet()

Dim i As Integer
i = ActiveSheet.Index - 1
If i > Sheets.Count Then i = 2
Sheets(i).Activate

End Sub

 

[Fluff]

That code neither misses the first sheet, nor checks for hidden sheets.
This will do what you have asked for

Sub PreviousSheet()

Dim i As Integer
i = ActiveSheet.Index - 1
If i = 1 Then i = Sheets.Count
Sheets(i).Activate

End Sub

 

[Balrajss0121]

Thank you for your response. I tried your code but unfortunately, it does cycle back through the sheets, as it stops when it gets to the first sheet. I would want it to carry on iterating through the sheets backwards without stopping, thanks.

 

[Jeffrey Mahoney]

I think you were asking to have the macro check if the sheet was not hidden and you wanted to step backward through the sheets and ignore the first sheet

Sub PreviousSheet()

  Dim i As Long
  Dim X As Long
  
  i = ActiveSheet.Index - 1
  
  For X = i To 1 Step -1
    If X = 1 Then X = Sheets.Count
    If Sheets(X).Visible = True Then
      Sheets(X).Activate
      Exit For
    End If
  Next X

End Sub

 

[Fluff]

No it doesn't stop when it gets to the first sheet, in fact it doesn't go to the first sheet.

 

[Fluff]

To ignore hidden sheets use.

Sub PreviousSheet()

Dim i As Integer
i = ActiveSheet.Index - 1
Do Until Sheets(i).Visible
   i = i - 1
Loop
If i = 1 Then i = Sheets.Count
Sheets(i).Activate

End Sub

 

[Balrajss0121]

I think you were asking to have the macro check if the sheet was not hidden and you wanted to step backward through the sheets and ignore the first sheet

Sub PreviousSheet()

  Dim i As Long
  Dim X As Long

  i = ActiveSheet.Index - 1

  For X = i To 1 Step -1
    If X = 1 Then X = Sheets.Count
    If Sheets(X).Visible = True Then
      Sheets(X).Activate
      Exit For
    End If
  Next X

End Sub

I think you were asking to have the macro check if the sheet was not hidden and you wanted to step backward through the sheets and ignore the first sheet

Sub PreviousSheet()

  Dim i As Long
  Dim X As Long
 
  i = ActiveSheet.Index - 1
 
  For X = i To 1 Step -1
    If X = 1 Then X = Sheets.Count
    If Sheets(X).Visible = True Then
      Sheets(X).Activate
      Exit For
    End If
  Next X

End Sub

Hi Jeffrey, I appreciate your response. Yes that is what I meant, I don't think I worded it very well. Your solution worked a treat. Thank you very much.

 

[Balrajss0121]

To ignore hidden sheets use.

Sub PreviousSheet()

Dim i As Integer
i = ActiveSheet.Index - 1
Do Until Sheets(i).Visible
   i = i - 1
Loop
If i = 1 Then i = Sheets.Count
Sheets(i).Activate

End Sub

Hi Fluff, thanks for the response, your second solution worked great. I most likely haven't worded it right and trust your judgment on the first solution working. Thank you

 

[Fluff]

One problem with my code is if the first sheet is hidden, use this instead.

Sub PreviousSheet()

Dim i As Integer
i = ActiveSheet.Index - 1
Do Until Sheets(i).Visible
  i = IIf(i = 1, Sheets.Count, i - 1)
Loop
Sheets(i).Activate

End Sub