Macro to go the the next worksheet

[rpaulson]

I have this macro to go to the next worksheet

this issue is, when it gets to the last sheet it wont re-start at the beginning .

any ideas?

Sub Next_Division()
Set Sh = ActiveSheet
On Error Resume Next
Do While Sh.Next.Visible <> xlSheetVisible
If Err <> 0 Then Exit Do
Set Sh = Sh.Next
Loop
Sh.Next.Activate
On Error GoTo 0
End Sub

Ross

 

[My Aswer Is This]

Try this:

Sub Sheet_Number()
'Modified 3-23-18 8:15 PM EDT
Dim ans As Long
ans = ActiveSheet.Index
    If ans = Sheets.Count Then
        Sheets(1).Activate
    Else
        Sheets(ans + 1).Activate
    End If
End Sub

 

[Rick Rothstein]

Here is a another macro that will advance to the next sheet and wrap back to the first sheet once the last sheet has been reached...

[table="width: 500"]
[tr]
	[td]Sub NextSheet()
  Sheets(ActiveSheet.Index + 1 + Sheets.Count * (ActiveSheet.Index = Sheets.Count)).Activate
End Sub[/td]
[/tr]
[/table]

And while you did not ask for it, here is the companion macro that moves through the sheets in reverse wrapping from the first sheet around to the last sheet once reached...

[table="width: 500"]
[tr]
	[td]Sub PreviousSheet()
  Sheets(ActiveSheet.Index - 1 - Sheets.Count * (ActiveSheet.Index = 1)).Activate
End Sub[/td]
[/tr]
[/table]

@rpaulson,

As as aside, I was just wondering if you saw what I posted to your "TOGGLEl page orientation VBA" thread earlier today? Here is a link directly to it...

https://www.mrexcel.com/forum/excel...-orientation-vba-post5036278.html#post5036278

 

[rpaulson]

Rick,

On the one liner about advancing to the next sheet, he wrap around is working. I wish to add another check. I would like to skip to the next sheet if the sheet does NOT have the Letter "X" in cell A1.
recap>>> advance to the next sheet if it is visible AND has an X in Cell A1.


Thanks for the reply. I just saw your reply to the "TOGGLEl page orientation VBA". that works great.!

Again thanks for all the help over the years.

Ross

 

[Rick Rothstein]

On the one liner about advancing to the next sheet, he wrap around is working. I wish to add another check. I would like to skip to the next sheet if the sheet does NOT have the Letter "X" in cell A1.
recap>>> advance to the next sheet if it is visible AND has an X in Cell A1.

Adding the condition removes the possibility of doing a one-liner as we now have to cater to the possibility that two or more consecutive sheets do not have an X in cell A1 as well as the possibility that either only one sheet has an X in cell A1 or that no sheets have an X in cell A1. This is what I came up with to ensure the code could handle all of these possibilities (for both the NextSheet and PrevSheet macros)...

[table="width: 500"]
[tr]
	[td]Sub NextSheet()
  Dim Cnt As Long, Idx As Long
  Idx = ActiveSheet.Index
  Do
    Cnt = Cnt + 1
    If Cnt = Sheets.Count Then Exit Sub
    Idx = Idx + 1
    If Idx > Sheets.Count Then Idx = 1
  Loop Until Sheets(Idx).Visible And Sheets(Idx).Range("A1").Value Like "[Xx]"
  Sheets(Idx).Activate
End Sub

Sub PreviousSheet()
  Dim Cnt As Long, Idx As Long
  Idx = ActiveSheet.Index
  Do
    Cnt = Cnt + 1
    If Cnt = Sheets.Count Then Exit Sub
    Idx = Idx - 1
    If Idx < 1 Then Idx = Sheets.Count
  Loop Until Sheets(Idx).Visible And Sheets(Idx).Range("A1").Value Like "[Xx]"
  Sheets(Idx).Activate
End Sub[/td]
[/tr]
[/table]

 

[rpaulson]

Rick,

do you accept PM's.?? I have a small project looking to work one-on-one with someone?

 

[Rick Rothstein]

Rick,

do you accept PM's.?? I have a small project looking to work one-on-one with someone?

Don't take this the wrong way, but I am not interested in working one-on-one with anyone. I have been retired going on 16 years now and only do this my Excel volunteering part time as time and circumstances permit. What you are suggesting sounds like it has the potential to require a more sustained and direct effort than I am willing to commit to. Sorry, but I hope you understand.

 

[rpaulson]

Rick,

No offense taken, i completely understand.

Maybe you can point me in the right direction.

I have an excel file that I have developed. I've been working on it a few months in my spare time, its 99% ready. I'm planning on selling it in the near future to a few customers. I'm looking for 2 things.

1. Someone to look at the file and see how I can protect it so that one customer can not give it to another one and both of them using it.
2. A way to push out future updates to users so than can add them to their existing file.

I'm plan on Paying someone for their time to accomplish this.
any suggestions on someone or some company that can help me out?

txs,

Ross

 

[Rick Rothstein]

At the top of this webpage is a menu bar (starts with "Forum", then "Search New Posts", etc.)... click the button labeled "Excel Consulting Services".