But if you really want the first worksheet with the name that matched that
string, then there isn't a real good reason to keep looking.



owlnevada wrote:

Interesting, after playing with both your solutions offered, after I made the
correction to delete the .name property as Rick suggested below and found it
worked perfectly, I went back to the first one and discovered that when I
posted just the very last line of your first solution with the Lbound
statement to my code, it worked perfectly also. Thought you should know.

Thanks again to both of you! It's been a great help.

"Dave Peterson" wrote:

I'm kind of confused at OneIWant is, but maybe this'll help:

Option Explicit
Sub Select1stSummary()

Dim i As Long
Dim Sheetnames() As String
Dim SumCount As Long

SumCount = 0
For i = 1 To Sheets.Count
If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then
' got a handle on a summary sheet -
' Save the name of the sheet in an array
SumCount = SumCount + 1
'so we can resize the array to the number of sheets
ReDim Preserve Sheetnames(1 To SumCount)
Sheetnames(SumCount) = Sheets(i).Name
End If
Next i

If SumCount = 0 Then
MsgBox "No sheets with Sum in the name!"
Exit Sub
End If

Sheets(Sheetnames).Select
Sheets(Sheetnames(UBound(Sheetnames))).Activate

End Sub

owlnevada wrote:

This code runs without error but is not selecting the first sheet in the
index that I want which selects only those sheetnames that have "sum" in the
character string of tabnames. The other worksheets would have something
other than "sum" as part of the name. Instead, it ends up with the highest
index numbered sheet as the active sheet and I want it to be the first or
index(1) in the string. It is not the same (1) for the entire workbook,
usually the 2nd or higher indexed sheet and the first part of the routine
narrows that down to a subset of worksheets within the workbook. Not sure if
the problem is that it is a string and needs to be an array or if the Select
Case is the wrong approach.

Any help is most appreciated!

Sub Select1stSummary()

'Selects All Summarys Macro groups summary sheets for further work as before

Dim i As Integer
Dim Sheetnames() As String
Dim Count As Integer
Dim ws As Sheets
Dim OneIwant As String

sumCount = 0
For i = 1 To Sheets.Count
If InStr(Sheets(i).Name, "sum") Then
' got a handle on a summary sheet -
' Save the name of the sheet in an array
Count = Count + 1 ' we need to count the sheets
ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array
to the number of sheets
Sheetnames(Count) = Sheets(i).Name
End If
Next i

OneIwant = Sheetnames(Count)
' Now all the names of the sheets that we want are in an array, select
them all at once
' On Error GoTo ErrorHandler 'Resume Next
' Exit Sub

On Error Resume Next
' With Sheets(Count)

Select Case Sheets(i)
Case 0
Case (i) = 1
Sheets(1).Select

End Select
'End With
End Sub

--

Dave Peterson


--

Dave Peterson