Select Case by Sheetname Property-String vs Array

[owlnevada]

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]

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]

Dave:

As written your code selects all the sheets in the workbook with "sum" in
their tabname. I changed the Ubound in the last line to Lbound and it still
did the same thing. "OneIwant" was supposed to be the individual worksheet
with the lowest index number(1) in the collection(?) of worksheets in the
workbook that match the "sum" as part of the tabname(or sheetname). I can
find a dozen different ways to swap things around in the code below the "
Next i" that don't have any syntax or runtime errors and it still activates
only the highest indexed sheet. Seems like I'm very close with that Select
Case approach.

Would something like reversing the "1 to Sheets.Count" to " Sheets.Count to
1" be closer to what I need so that regardless of the total count, it ends
up activating the first indexed sheet? Does this need a -1 in there to make
it step backwards?

 

[Dave Peterson]

If you only want the first sheet that matches that has Sum in its name:

Option Explicit
Sub Select1stSummary()

Dim i As Long
Dim OneIWant as worksheet

set oneiwant = nothing
For i = 1 To Sheets.Count
If InStr(1, Sheets(i).Name, "sum", vbTextCompare) > 0 Then
set oneIwant = sheets(i).name
exit for 'stop looking
End If
Next i

If oneiwant is nothing then
MsgBox "No sheets with Sum in the name!"
Exit Sub
End If

oneiwant.Select

End Sub

 

[owlnevada]

As written, this comes up with "Object required" error on second pass when
the leftmost sheet is named "Sheet1" and 2nd sheet has "sum" in the name.
Error appears at the Set OneIWant = Sheets(i).name. What would the object
be? Sheets, Sheetnames, etc. I tried "dim sheets as object" and it gave
different errors . . . object variable or with block variable not set.

 

[Rick Rothstein \(MVP - VB\)]

Dave accidentally left the property on the range in this statement...

set oneIwant = sheets(i).name

Change the above statement to this...

set oneIwant = sheets(i)

Rick

As written, this comes up with "Object required" error on second pass when
the leftmost sheet is named "Sheet1" and 2nd sheet has "sum" in the name.
Error appears at the Set OneIWant = Sheets(i).name. What would the object
be? Sheets, Sheetnames, etc. I tried "dim sheets as object" and it gave
different errors . . . object variable or with block variable not set.

 

[Dave Peterson]

Stupid fingers!!!!

Thanks for the correction, Rick.

 

[owlnevada]

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]

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]

I see that the last one is the shortest most efficient code so will use it.

To get back to my first post, in my approach using the Select Case, in order
to bolster my understanding to accomplish my tasks, is there an easy
modification to that that you can offer or is it just a poor way to do the
same thing? If there is, then I might find that useful so that I could say
modify that to select a certain group of sheets that have some common
characters like "#####-sum-2" scattered thruout the workbook?

 

[Dave Peterson]

I think I'd continue to use the loop, but then use a Like comparison.

The code is essentially the same...

SumCount = 0
For i = 1 To Sheets.Count
If LCase(Sheets(i).Name) Like "#####-sum*" 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


I'm assuming that ##### represents 5 digits, right?

or maybe
If LCase(Sheets(i).Name) Like "#####-sum-2" Then

I'm not sure what the pattern actually is.

I see that the last one is the shortest most efficient code so will use it.

To get back to my first post, in my approach using the Select Case, in order
to bolster my understanding to accomplish my tasks, is there an easy
modification to that that you can offer or is it just a poor way to do the
same thing? If there is, then I might find that useful so that I could say
modify that to select a certain group of sheets that have some common
characters like "#####-sum-2" scattered thruout the workbook?

 

[owlnevada]

Correct, the ##### represent unique permit numbers for sets of worksheets.
When the tabname includes "sum-2", "sum-3", those are the sheets I want to
select as a group for further work. "Sum-*" is for multiple pages when more
than a single page. If just a one page sum, the its just "#####sum" and can
be selected easily with existing code.

I ran this code without errors but it makes no selection. What else needs to
be coded? I removed the .name as before and it made no difference. I would
like an input box to ask for the page number, then continue to select them,
so that I need to code it only once. My need comes from thousands of files
that have combinations of single and mulitiple pages of sheets with "sum*" in
the name and need to be selected easily for further work as a group.

 

[Dave Peterson]

Maybe...

If LCase(Sheets(i).Name) Like "#####-sum*" _
or LCase(Sheets(i).Name) Like "#####sum" Then

If that doesn't help, I think it's time for you to post your current code.

Correct, the ##### represent unique permit numbers for sets of worksheets.
When the tabname includes "sum-2", "sum-3", those are the sheets I want to
select as a group for further work. "Sum-*" is for multiple pages when more
than a single page. If just a one page sum, the its just "#####sum" and can
be selected easily with existing code.

I ran this code without errors but it makes no selection. What else needs to
be coded? I removed the .name as before and it made no difference. I would
like an input box to ask for the page number, then continue to select them,
so that I need to code it only once. My need comes from thousands of files
that have combinations of single and mulitiple pages of sheets with "sum*" in
the name and need to be selected easily for further work as a group.

 

[owlnevada]

Here it is. I am trying to array the page numbers when they are 2 or
greater, get the value of the page number I'm looking for from the input box,
then select all those that have the page number I want. I only need to look
for the digits 1-19
after the word "#####sum-". I now have a syntax error on the line beginning
with If LCase. . . Seems like we would maybe have two arrays, one called
sheetnames and one for page numbers? This is where I'm lost.

Many Thanks once again.


Private Sub SelectAllSumOnePageNum() 'try to code once to select any page #
_using an InputBox


Dim sheetnames As Variant
Dim PNum As Integer

SumCount = 0

InputBox "What is the page number (>=2) you want to select?, Page Number
Selection ,2"

For i = 1 To sheets.count
If LCase(sheets(i).Name "(PNum)" Like InStr(1, sheets(i).Name(PNum),
"sum-""(PNum)", 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(PNum)
End If
Next
End Sub

 

[Dave Peterson]

Just to make sure...

There is no dash before the word "sum". It's 5 digits followed by "sum"
followed by a hyphen, followed by that page number, right?

And you want to select all the sheets that match that naming convention?

Option Explicit
Sub SelectAllSumOnePageNum()
'try to code once to select any page # using an InputBox

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

SumCount = 0

PNum = Application.InputBox _
(Prompt:="What is the page number (>=2) you want to select?", _
Title:="Page Number Selection", Default:=2, Type:=1)

If PNum < 2 Then
Exit Sub
End If

For i = 1 To Sheets.Count
If LCase(Sheets(i).Name) Like LCase("#####sum-" & PNum) Then
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 match that pattern!"
Exit Sub
End If

Sheets(Sheetnames).Select

End Sub

I used Application.inputbox with type:=1 so that I didn't have to verify that
the user's entry was numeric. If I had used VBA's inputbox, then I'd have to
check.

Here it is. I am trying to array the page numbers when they are 2 or
greater, get the value of the page number I'm looking for from the input box,
then select all those that have the page number I want. I only need to look
for the digits 1-19
after the word "#####sum-". I now have a syntax error on the line beginning
with If LCase. . . Seems like we would maybe have two arrays, one called
sheetnames and one for page numbers? This is where I'm lost.

Many Thanks once again.

Private Sub SelectAllSumOnePageNum() 'try to code once to select any page #
_using an InputBox

Dim sheetnames As Variant
Dim PNum As Integer

SumCount = 0

InputBox "What is the page number (>=2) you want to select?, Page Number
Selection ,2"

For i = 1 To sheets.count
If LCase(sheets(i).Name "(PNum)" Like InStr(1, sheets(i).Name(PNum),
"sum-""(PNum)", 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(PNum)
End If
Next
End Sub

 

[owlnevada]

Right on both questions. Page one name is just "#####sum" with no dash or
number 1. I tried this on a test file with ". . .sum-2" at index position
2,9, and 12 in a 14 sheet workbook. . . curiously, it selected indexed sheets
9 and 12 just fine but didn't select the one at position 2. . . any idea on
that?

 

[Dave Peterson]

Share the exact names of those 3 sheets.
Share what you typed into the inputbox.

Look out for leading and trailing spaces in those worksheet names, too.

And there's a difference between:
12345sum-0002
and
12345sum-2

Right on both questions. Page one name is just "#####sum" with no dash or
number 1. I tried this on a test file with ". . .sum-2" at index position
2,9, and 12 in a 14 sheet workbook. . . curiously, it selected indexed sheets
9 and 12 just fine but didn't select the one at position 2. . . any idea on
that?

 

[owlnevada]

I accepted the default 2 by clicking OK. The sheets are 3530sum-2,
72823sum-2, and 75296sum-2, or indexed as in the order stated previously. I
checked the exact names by using my macro which does that, called ingeniously
"Sheetnames" and ran it again with the same results. . . selecting the last
two only.

 

[owlnevada]

I see the problems now at the LCase line. . . so I removed one of the #, ran
it again, and of course it selected only the 3530sum-2 and not the other two.
.. . so we need to get all the sums that can have from 1 to 5 digits . . .
hoping using a wildcard for those # will work?

 

[Dave Peterson]

How about just looking for (anything)Sum-(pagenumber)?

If LCase(Sheets(i).Name) Like LCase("*sum-" & PNum)