I have a WorkBook with 200+ Worksheets. I sort these A-Z with a VBA code.

Somewhere I read a tip on how to GoTo a Worksheet directly without scrolling
the Tabs. Am I dreaming?

Bob M.

Try one of these macros....

'Enter exact sheet name
Sub Macro1()
Dim strSheet As String, ws As Worksheet
strSheet = InputBox("Enter Sheet Name")

On Error Resume Next
Set ws = Sheets(strSheet)

If ws Is Nothing Then
MsgBox "Cannot find the sheet"
Else
Sheets(strSheet).Activate
End If

End Sub


'Enter a string..which match the sheetname
Sub Macro2()
Dim strSheet As String, ws As Worksheet
strSheet = InputBox("Enter Search String")
For Each ws In Sheets
If ws.Name Like "*" & strSheet & "*" Then ws.Activate: Exit Sub
Next
MsgBox "Cannot find a sheet with search string " & strSheet
End Sub

--
Jacob


"robert morris" wrote:

I have a WorkBook with 200+ Worksheets. I sort these A-Z with a VBA code.

Somewhere I read a tip on how to GoTo a Worksheet directly without scrolling
the Tabs. Am I dreaming?

Bob M.

If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()

--
Jacob


"robert morris" wrote:

I have a WorkBook with 200+ Worksheets. I sort these A-Z with a VBA code.

Somewhere I read a tip on how to GoTo a Worksheet directly without scrolling
the Tabs. Am I dreaming?

Bob M.

Well you can right click on the 4 x Navigation Arrows at the bottom left of
the screen and get a list of all the worksheets although it only displays 15
at a time ...

"robert morris" wrote:

I have a WorkBook with 200+ Worksheets. I sort these A-Z with a VBA code.

Somewhere I read a tip on how to GoTo a Worksheet directly without scrolling
the Tabs. Am I dreaming?

Bob M.

don't know what you saw, maybe right click the navigation arrows and select
it from the list.
--


Gary Keramidas
Excel 2003


"robert morris" wrote in message
...
I have a WorkBook with 200+ Worksheets. I sort these A-Z with a VBA code.

Somewhere I read a tip on how to GoTo a Worksheet directly without
scrolling
the Tabs. Am I dreaming?

Bob M.

Jacob,

Thanks for your reply. I am familar with VBA. I will try your code and let
you know the result. Today however is a really loaded work day.

Thanks,

Bob. M.

"Jacob Skaria" wrote:

Try one of these macros....

'Enter exact sheet name
Sub Macro1()
Dim strSheet As String, ws As Worksheet
strSheet = InputBox("Enter Sheet Name")

On Error Resume Next
Set ws = Sheets(strSheet)

If ws Is Nothing Then
MsgBox "Cannot find the sheet"
Else
Sheets(strSheet).Activate
End If

End Sub


'Enter a string..which match the sheetname
Sub Macro2()
Dim strSheet As String, ws As Worksheet
strSheet = InputBox("Enter Search String")
For Each ws In Sheets
If ws.Name Like "*" & strSheet & "*" Then ws.Activate: Exit Sub
Next
MsgBox "Cannot find a sheet with search string " & strSheet
End Sub

--
Jacob


"robert morris" wrote:

I have a WorkBook with 200+ Worksheets. I sort these A-Z with a VBA code.

Somewhere I read a tip on how to GoTo a Worksheet directly without scrolling
the Tabs. Am I dreaming?

Bob M.

You can right-click on the navigation arrows at bottom left and get a
list of 15 sheets and "more sheets" to choose from.

But with 200+ sheets.................

How about a sheet navigation toolbar from Dave Peterson?

http://www.contextures.on.ca/xlToolbar01.html

Or Bob Phillips' Browsesheets macro which I like best, but that's me<g

See this google search result. All one line for the URL

http://groups.google.com/group/micro...c19464f875bd25



Gord Dibben MS Excel MVP

On Thu, 18 Mar 2010 22:10:01 -0700, robert morris
wrote:

I have a WorkBook with 200+ Worksheets. I sort these A-Z with a VBA code.

Somewhere I read a tip on how to GoTo a Worksheet directly without scrolling
the Tabs. Am I dreaming?

Bob M.