Move next-previous sheet with button

[ybr_15]

Dear members, I have 5 sheets in my workbook (Sheet1, Sheet2,... Sheet5). I want create a button to move next-previous on selected sheets. Example, I want to move from Sheet2 to Sheet5 and back again from Sheet5 to Sheet2. Here my code:
Button in Sheet2:

Sub MoveOn ()[INDENT]Worksheets("Sheet5").Activate[/INDENT]
[INDENT]ActiveSheet.Range("A1").Select[/INDENT]
End Sub

Button in Sheet5:

Sub MoveBack ()[INDENT]Worksheets("Sheet2").Activate[/INDENT]
[INDENT]ActiveSheet.Range("A1").Select[/INDENT]
End Sub

I know that code work perfectly but I want to reduce the code with one macro only. I tried make new code but doesn't work properly. This code can move from Sheet2 to Sheet5 but not the other way.

Sub MoveOn_Back ()[INDENT]If Worksheets("Sheet2").Visible = True Then[/INDENT]
[INDENT=2]Worksheets("Sheet5").Activate[/INDENT]
[INDENT=2]ActiveSheet.Range("A1").Select[/INDENT]
[INDENT]Else[INDENT]Worksheets("Sheet2").Activate[/INDENT]
[INDENT]ActiveSheet.Range("A1").Select[/INDENT]
End If
[/INDENT]
End Sub

Can anyone solve this problem? Thanks for answering (my stupid question)

 

[vcoolio]

Hello Ybr_15,


You could create hyperlinks on each sheet but why not just click on the sheet tab that you wish to move to?

Cheerio,
vcoolio.

 

[Michael M]

THis will work....but I don't see the point when the sheets would be accessible by a single click ????

Sub MM1()
    If ActiveSheet.Name = "Sheet2" Then
        Worksheets("Sheet5").Activate
    Else
        Worksheets("Sheet2").Activate
    End If
End Sub

 

[ybr_15]

Hello vcoolio, I know I can use hyperlink but I am just curious how to do that with vba code.

---

Thanks to Michael M for your answer. Your code work properly. I agree with your statement about:

I don't see the point when the sheets would be accessible by a single click ????

I just want to reduce the code so that the number of macro names can be reduced.

 

[vcoolio]

Hello Ybr_15,

The following code will create hyperlinks in each sheet:-

Sub CreateHLink()

Dim i As Long
Dim ws As Worksheet

For Each ws In Worksheets
    For i = 1 To Sheets.Count
        ws.Cells(i, 1) = Sheets(i).Name
    Next i
Next

For Each ws In Worksheets
       For i = 1 To ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Hyperlinks.Add Anchor:=ws.Range("A" & i), Address:="", SubAddress:= _
              "'" & ws.Range("A" & i).Value & "'!A1", TextToDisplay:=ws.Range("A" & i).Value
        Next i
   Next
   
End Sub

With this code, the sheet names will be listed in Column A of each sheet as hyperlinks so you can move from any sheet to any sheet by clicking on the hyperlink of the required sheet. You can, of course, place the list in any column you wish. You will only need to run the code as new sheets are added.

I still think that just clicking on the sheet tab is the simplest way of moving from sheet to sheet or, if the number of sheets becomes very large, you can right click on the command bars at the bottom left of any sheet, a sheet list will appear then just select the sheet that you wish to move to.

Test the above code in a copy of your work book or a new blank work book.

Anyway, I hope that this helps.

Cheerio,
vcoolio.