Macro to advance to next worksheet

Android

New Member
Joined
Mar 11, 2009
Messages
2
I have a workbook (2007) with about 55 worksheets. I would like to do any one of the following:

On the main worksheet "home page" add a macro that allows me to select which worksheet I want to go to, and when taken to that worksheet have another macro that takes me back to the "home page".

Or (more ideally)

On the "home page" have a macro that allows me to go to a specific worksheet, either through a pop up, or a scroll bar. And on each of the worksheets have 3 buttons namely <Previous> <Home> <Next>

Any ideas?

Thanks in advance.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
Welcome to the board, Android. Short of going to each sheet and creating a button, try this macro.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> macro1()<br><br><SPAN style="color:#00007F">Dim</SPAN> MyPrompt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyTitle <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>Y = "Sheet"<br>MyPrompt = "Enter Sheet Number You Wish."<br>MyTitle = "* * * Enter Number Only * * *"<br><br>yesno = MsgBox("Do you wish to go to the homepage?", vbYesNo)<br><SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> yesno<br><SPAN style="color:#00007F">Case</SPAN> vbYes<br>Worksheets("Home Page").Select<br><SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Case</SPAN> vbNo<br>Response = Application.InputBox(Prompt:=MyPrompt, _<br>    Title:=MyTitle)<br>   <br>    <SPAN style="color:#00007F">If</SPAN> Response = "False" <SPAN style="color:#00007F">Then</SPAN> MsgBox "Program Cancelled"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Response = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    Z = Response<br><br>Sheets("Sheet" & (Z)).Select<br><SPAN style="color:#00007F">End</SPAN> Select<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Every time you want to change sheets, activate the macro, otherwise, create a button one ach sheet that goes to this macro. This macro assumes that your homepage is named "Home Page" and the other sheets do not have unique names, although I believe that even if they do, this will still work on them.

If you do go to the trouble of going to each page to place a button on it, you might as well create a link to the homepage next to the button, and then you will need to only to include this in the macro:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> macro1()<br><br><SPAN style="color:#00007F">Dim</SPAN> MyPrompt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyTitle <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>Y = "Sheet"<br>MyPrompt = "Enter Sheet Number You Wish."<br>MyTitle = "* * * Enter Number Only * * *"<br><br><br><SPAN style="color:#00007F">Case</SPAN> vbNo<br>Response = Application.InputBox(Prompt:=MyPrompt, _<br>    Title:=MyTitle)<br>   <br>    <SPAN style="color:#00007F">If</SPAN> Response = "False" <SPAN style="color:#00007F">Then</SPAN> MsgBox "Program Cancelled"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Response = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    Z = Response<br><br>Sheets("Sheet" & (Z)).Select<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH's, and again, welcome!
 
Last edited:

Dougie1

Board Regular
Joined
Jul 27, 2007
Messages
212
Do you really need a macro ??

Why not just navigate between sheets by right clicking on the directional arrows at the very bottom left of the sheet and using the sheet menu there to move between sheets.

Cheers,

D
 

Android

New Member
Joined
Mar 11, 2009
Messages
2
Bill_Biggs - Thank you for the input, very much appreciated, and it works.

Dougie1 - You are right, but with a Macro it is easier to navigate.

Thanks again guys, very useful resource.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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