Selecting ONLY sheets meeting criteria while other sheets are active

[riley454]

I am attempting to select all sheets with names containing "* +H" (there are many) to allow me to paste a block of data to all sheets selected.

The following block of code does what I need however if there were any sheets already selected it adds these sheets to the selection even if they don't meet the "* +H" criteria.

What is the best way to add to this code to deselect any selected sheets that don't qualify? Or should I attack this from a different angle?

No doubt there are multiple ways to achieve this but hoping for something simple without bloating my code.

I've run the same code twice with the first time "Replace:=True" and second time "Replace:=False". It works but seems like a dodgy workaround.

Is there a "nicer" way?

For j = 1 To ThisWorkbook.Sheets.Count
    If Sheets(j).Name Like "* +H" Then Sheets(j).Select Replace:=False
Next j

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try like this

Dim j As Long
Dim bStarted As Boolean

For j = 1 To ThisWorkbook.Sheets.Count
  If Sheets(j).Name Like "* +H" Then
    Sheets(j).Select Replace:=Not bStarted
    bStarted = True
  End If
Next j

 

[riley454]

Thanks Peter I have updated my profile to show I am using 2010

I'm not sure if thats why your edited version of my code did not work though. All currently selected sheets from my prior process were still selected after applying my filter for the new range of sheets

 

[Peter_SSs]

I have updated my profile to show I am using 2010

Thanks for doing that.

I think there should be no problem with the code because of 2010.

Are you saying that
- all the previously selected sheets were still selected as well as the "* +H" sheets, or
- all the previously selected sheets were still selected but the code did not add any "* +H" sheets.

Is the code combined with any other code or is is exactly as shown (apart from a 'Sub' line above and 'End Sub' line below?
If there is other code can you show us the entire code?

Do you have any other code in the workbook (eg Event code)?

 

[riley454]

Are you saying that
- all the previously selected sheets were still selected as well as the "* +H" sheets, or
- all the previously selected sheets were still selected but the code did not add any "* +H" sheets.

Prior to this block of code running, many sheets in this large workbook have already been selected for other purposes including all of the "+H" sheets. Running your modified code did not need to add the "+H" sheets as they were already selected and the previously selected sheets that do not include "+H" were still selected.

Is the code combined with any other code or is is exactly as shown (apart from a 'Sub' line above and 'End Sub' line below?
If there is other code can you show us the entire code?

Do you have any other code in the workbook (eg Event code)?

There is much more code before and after the small snippet I have provided. There are a lot of individual blocks much like the one I have shown and mostly they involve straightforward processes such as creating/naming new sheets from a "source" data sheet and copy/pasting from various template sheets within this book to relevant sheets and ranges that meet certain criteria (such as sheetname Like "* H"). There are no "Event" codes or anything that I expect would conflict with selecting/deselecting the required sheets.

 

[Peter_SSs]

... many sheets in this large workbook have already been selected for other purposes including all of the "+H" sheets.

First thing that I would try is to get your workbook in the state mentioned above and then run this as a stand-alone procedure after putting a break point on the blue line. When/if the code stops at the break point press F8 to process that line and see if initially that one single sheet gets selected and the other de-selected.

Sub TestSelectSheets()
  Dim j As Long
  Dim bStarted As Boolean
  
  For j = 1 To ThisWorkbook.Sheets.Count
    If Sheets(j).Name Like "* +H" Then
      Sheets(j).Select Replace:=Not bStarted
      bStarted = True
    End If
  Next j
End Sub

 

[riley454]

Thanks again Peter I appreciate your persistence

I have stepped through with F8 many times with many variations of my code and your variations.

I feel the shortfall is my inability to "deselect" sheets that are not required that have previously been selected.

Is there a way to modify Select/Replace=TRUE for the first instance of "+H" and then change to Replace=FALSE for the rest? Perhaps some way to throw in a line before/after "Next j"

As previously mentioned I can run this code with "replace=true" for all sheets to end up with only a "+H" sheet selected then run it again with "replace=false" to achieve the result I'm after but it seems very inefficient

For j = 1 To ThisWorkbook.Sheets.Count
If Sheets(j).Name Like "* +H" Then Sheets(j).Select Replace:=True
Next j

'If (NEXT j SHEET) Sheets (j).Name Like "* +H" Then Sheets(j).Select Replace:=False

Next j

I either need to deselect those sheets that don't qualify which I think is not easy for excel particularly if it is THE active sheet, or select the first qualifying sheet as the only selected sheet then continue with my code as provided

 

[Peter_SSs]

I have stepped through with F8 many times with many variations of my code and your variations.

But have you stepped through with only my code?
If so, when you first get to the blue line, before that line is executed, can you hover over bStarted and confirm that it is FALSE and as soon as that line is executed has bStarted changed to TRUE?

Is there a way to modify Select/Replace=TRUE for the first instance of "+H" and then change to Replace=FALSE for the rest?

That is exactly what my code does.

When bStarted is declared a Boolean, the default value is FALSE. So the first time the line
Sheets(j).Select Replace:=Not bStarted
is executed it is
Sheets(j).Select Replace:=Not FALSE
therefore
Sheets(j).Select Replace:=TRUE

The following line then sets bStarted to TRUE so every other time if the blue line is executed it becomes
Sheets(j).Select Replace:=Not TRUE
therefore
Sheets(j).Select Replace:=FALSE

I have tested the code with many different variations of sheets selected before my code is run and in all instances I end up with only the * +H sheets selected.
Perhaps you could upload a sanitised version of your file to DropBox/One Drive/Google Drive and provide a shared link here so that further investigation can be undertaken?

 

[riley454]

Thanks Peter you have solved my problem.

I had initially misinterpreted your troubleshooting advice but also found something else in the larger body of my code was tripping me up.

Problem was solved using your method and much cleaner than what I was going to revert to

 

[Peter_SSs]

Problem was solved using your method and much cleaner than what I was going to revert to

Cheers. Glad it is resolved.
Thanks for letting us know.

.. but also found something else in the larger body of my code was tripping me up.

I thought that might be the case, hence my suggestion of ..

.. run this as a stand-alone procedure ..

.. to eliminate the influence of other parts of your code.