Macro Button to go to specific sheet

[Mr.S 55]

Hi,

I am new to VBA and Macros. I am trying to create a button that takes you from a home page to a specified sheet. I need to make about 20 buttons. Any help in this matter would be much appreciated.

[mehmetcik]

Hi

Firstly you probably do not need to do anything. Excel does that automatically.

RIGHT CLICK your mouse cursor over the arrows at the bottom left of excel.

A list of sheets will open select one to move there.

Secondly I wrote the attached macros to do something similar.

I will send them seperately.

[mehmetcik]

This macro creates a sheet called index and lists all the sheets in your workbook:

Sub Contents()

On Error GoTo 40
    Sheets("Index").Select
    
 GoTo 50
40   Sheets.Add
     Sheets(ActiveSheet.Name()).Name = "Index"
     Sheets("Index").Move Before:=Sheets(1)
    GoTo 50
      
50  Columns("A:A").Select
    Selection.ClearContents
    
60  myshts = ActiveWorkbook.Sheets.count

On Error GoTo 200

100 For count = 1 To myshts

    Sheets(count).Select

    TEMP = ActiveSheet.Name
    Sheets("Index").Select
    Range("A" & count).Select
    ActiveCell.FormulaR1C1 = TEMP
  
    Next
    
200     ' Exit
End Sub

This macro sorts all your workbook sheets to match your index.
So if you want your sheets sorted alphabetically for example sort the index sheet and run this Macro.

Sub Sortlist()

On Error GoTo 200

   Sheets("Index").Select
    
count = 1

10 Sheets("Index").Select
    count = count + 1

    Target = Range("A" & count).Value()
    
    Sheets(Target).Select
    ActiveSheet.Move ActiveWorkbook.Sheets(count)
      
    Sheets("Index").Select
   
    GoTo 10
   
200 ' END

End Sub

This next macro is quite powerful. I use ctrl g to run it.
press ctrl g on any sheet,
if it is used on the index sheet it will take you to the sheet named in the active row.
On any other sheet it takes you to the Index Sheet.


Sub GOTOSHEET()

If ActiveSheet.Name() <> "Index" Then GoTo 100

Range("A" & ActiveCell.Row()).Select

Target = ActiveCell.Value()
If Target = "" Then GoTo 200
Sheets(Target).Select

GoTo 200

100 On Error goto 200

Sheets("Index").Select

200 ' exit

End Sub


Finally the attached file contains a file with an auto open macro. this can also be triggered using ctrl shift g.

This opens a userform that lists all the sheets in your userform.

Click on a sheet name to go there.

Enjoy,

[mehmetcik]

Sorry

I sent wrong userform.