Sort Worksheets

[robert morris]

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 Skaria]

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 Skaria]

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()>

 

[Matt Geare]

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 ...

 

[Gary Keramidas]

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

 

[robert morris]

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.

 

[Gord Dibben]

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/micr...464f875bd25?lnk=st&q=&rnum=1#02c19464f875bd25



Gord Dibben MS Excel MVP