Macro to go the the next worksheet

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,170
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?

Code:
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows
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...
Code:
[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...
Code:
[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
 
Last edited:

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,170
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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)...
Code:
[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

Well-known Member
Joined
Oct 4, 2007
Messages
1,170
Rick,

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 4, 2007
Messages
1,170
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows
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".
 

Watch MrExcel Video

Forum statistics

Threads
1,129,364
Messages
5,635,839
Members
416,885
Latest member
hs11

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
Top