Navigating Worksheets

see the arrows in the bottom left of the worksheet?

Right click on them and you should be presented with a list of worksheet names.

select whichever one you want to move to

HTH

I presume you are using Windows rather than a Mac.

Use Ctrl + Page Down to move forward a worksheet

Ctrl + Page Up moves backwards

NB.Ensure Num Lock is off

Here's a macro that makes a hyperlinked index sheet (any chart sheets will be replaced with regular sheets with the chart as an object):

Sub MakeIndexSheet()
Dim i As Integer
Dim Sheetname As String

intResponse = MsgBox("This macro will create an index page of the worksheets in the active workbook." & vbCrLf & "Any worksheet named 'Index Sheet' will be replaced." & vbCrLf & vbCrLf & "NOTE: any chart worksheets will be converted to regular worksheets" & vbCrLf & "with the chart inserted as an object", vbInformation + vbOKCancel, "Create Index Page")
If intResponse = vbOK Then
On Error Resume Next
Application.ScreenUpdating = False
Sheets("Index Sheet").Select
Select Case Err.Number
Case 9
Sheets.Add Sheets(1)
Sheets(1).Select
ActiveSheet.Name = "Index Sheet"
On Error GoTo 0
Case 0
Application.DisplayAlerts = False
Sheets("Index Sheet").Delete
Application.DisplayAlerts = True
Sheets.Add Sheets(1)
Sheets(1).Select
ActiveSheet.Name = "Index Sheet"
On Error GoTo 0
Case Else
GoTo errorhandler
End Select

On Error GoTo errorhandler
Sheetcount = ActiveWorkbook.Sheets.count
Range("D2").Select
ActiveCell.Value = "Contents"
ActiveCell.Font.Size = 16
ActiveCell.Offset(0, 1).FormulaR1C1 = _
"To return to the index page, right-mouse click on the worksheet scroll buttons 34"
With ActiveCell.Offset(0, 1).Characters(Start:=80, Length:=2).Font
.Name = "Marlett"
.FontStyle = "Regular"
.Size = 10
End With

ActiveCell.Offset(2, 0).Select
For i = 1 To Sheetcount
If Sheets(i).Type = -4167 Then
temp = "'" & Sheets(i).Name & "'"
If Sheets(i).Name <> "Index Sheet" Then
ActiveCell.Value = Left(temp, Len(temp) - 1)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=temp & "!A1"
'
' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
' temp & "!A1", TextToDisplay:=Left(temp, Len(temp) - 1)
ActiveCell.Font.Size = 12
ActiveCell.Offset(1, 0).Select
End If
Else 'Selected sheet is a Chart sheet
Sheets(i).Select
On Error Resume Next
Sheets.Add
On Error GoTo 0
Sheets(i).Select
NewSheetname = ActiveSheet.Name
OldSheetname = Sheets(i + 1).Name
Sheets(i + 1).Select
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsObject, Name:=NewSheetname
ActiveSheet.Shapes(1).ScaleWidth 1.48, msoFalse, msoScaleFromBottomRight
ActiveSheet.Shapes(1).ScaleHeight 1.48, msoFalse, msoScaleFromBottomRight
ActiveSheet.Shapes(1).ScaleWidth 1.29, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(1).ScaleHeight 1.28, msoFalse, msoScaleFromTopLeft
DoEvents
ActiveSheet.ChartObjects(1).Select
With Selection.Font
.Size = 10
.Bold = True
End With
ActiveSheet.Name = OldSheetname
Windows(ActiveWorkbook.Name).Activate
Range("a1").Select
ActiveWindow.DisplayGridlines = False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With
Sheets("Index Sheet").Select
temp = "'" & Sheets(i).Name & "'"
ActiveCell.Value = Left(temp, Len(temp) - 1)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=temp & "!A1"
ActiveCell.Font.Size = 12
ActiveCell.Offset(1, 0).Select
End If
Next i
Columns("D:D").EntireColumn.AutoFit
Range("A1").Select
ActiveWindow.DisplayGridlines = False
On Error Resume Next
ActiveSheet.SetBackgroundPicture Filename:= _
"C:\Program Files\Common Files\Microsoft Shared\Stationery\Ivy.gif"
On Error GoTo 0
ActiveWindow.DisplayHeadings = False
Range("D2").Select

End If
errorhandler:
If Err <> 0 Then
MsgBox Err.Number & ": " & Err.Description
On Error GoTo 0
End If
Application.ScreenUpdating = True
End Sub

The quickest way is to download theasap addin from http://www.asap-utilities.com. There is a function there to create a hyperlinked index page (it's in the "Sheets" sub menu)
This addin is free and it's invaluable.

Hi,

The problem with building up an TOC-sheet is that we can easily move from the sheet to another sheet but then we need to use another "shortcut" to move to next.

So if You don't want to build up a VBA-solution then go with the "right-click" and if You want a VBA-solution following might be of interest, whic create a Navigation-commandbar:

Option Explicit

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Navigate").Delete
On Error GoTo 0

With Application.CommandBars.Add("Navigate XL-Dennis", , False, True)

With .Controls.Add(msoControlButton)
.TooltipText = "Move Back"
.FaceId = 1017
.OnAction = "Move_Back"
.BeginGroup = True
End With

With .Controls.Add(msoControlDropdown)
.AddItem "Sheet1"
.AddItem "Sheet2"
.AddItem "Sheet3"
.TooltipText = "SheetNavigate"
.OnAction = "Sheet_Navigate"
End With

With .Controls.Add(msoControlButton)
.TooltipText = "Move next"
.FaceId = 1018
.OnAction = "Move_Next"
End With

.Protection = msoBarNoCustomize
.Position = msoBarFloating
.Visible = True
End With
End Sub

Private Sub Sheet_Navigate()
Dim stActiveSheet As String

With CommandBars.ActionControl
stActiveSheet = .List(.ListIndex)
End With

Select Case stActiveSheet
Case "Sheet1"
Worksheets("Shee1").Activate
Case "Sheet2"
Worksheets("Sheet2").Activate
Case "Sheet3"
Worksheets("Sheet3").Activate
End Select
End Sub

Private Sub Move_Back()
On Error Resume Next
ActiveSheet.Previous.Select
End Sub

Private Sub Move_Next()
On Error Resume Next
ActiveSheet.Next.Select
End Sub

Kind regards,
Dennis

Or try this little baby !

Edit:WillR[Y]

Re: Navigating Worksheets

Hi All,

I was looking for a solution for navigating through worksheets, while the "right-click" option is good, I don't want every time do the right click and do the rest of the job.

Is there any short cut way by which I can get the list of sheets displayed(preferably by using key board short cut)..

In short I want the "right-click" option to be assigned to a key board short cut, instead of a mouse click..

hope I made it clear

Please help.

thanx
Pradeep