Select Worksheets based on criteria

[NewDaddy]

Hi all

Have been visiting the site for a little while now and the day has come that I needs some help.

I would like a routine/proceedure that will loop through all the sheets in a workbook and select all those that meet a certain criteria.

One example I would like to do is select all sheets with an '@' in its sheet name.

Any help much appriciated.

Cheers
Jay

[mvidas]

Hi Jay,

Glad we could finally be of assistance for you :) To select multiple sheets you have to put their names into an array, I hope the following example explains everything for you:


Sub NewDaddySelectSheets()
Dim sArr() As String, Cnt As Long, WS As Object
ReDim sArr(0)
Cnt = 0
For Each WS In ActiveWorkbook.Sheets
If InStr(WS.Name, "@") Then
ReDim Preserve sArr(Cnt)
sArr(Cnt) = WS.Name
Cnt = Cnt + 1
End If
Next
If Cnt > 0 Then Sheets(sArr).Select
Set WS = Nothing
End Sub
If you have any questions, please ask!
Matt

[NewDaddy]

Hi Matt

Thank you.
I new I had to build an array and sort of had an idea for doing so but could not workout how to select them.

I am just about to give it a run on a file I am working on now.
I'll let you know how I get on.

Cheers
Jay

[NewDaddy]

Hi Matt

Thanks for your help. I have managed to use this in a few scenarios already!

Have another one that I need some help with.
Using the above I would like ‘toggle’ hiding/showing the worksheets based in the array.
I have some that I use to do this but it refers specifically to a worksheet(s) and I am not sure how to ‘assign?’ the various worksheets in the array to the visible true/false?

I am looping through all worksheets in the active workbook and building an array of worksheet names that are like “TP Grid*”.

And the vba I use and not sure how to adapt is as follows;


On Error Resume Next
If Application.Worksheets("Aptos").Visible Then
Application.Worksheets("Aptos").Visible = False
ElseIf Application.Worksheets("Aptos").Visible = False Then
Application.Worksheets("Aptos").Visible = True
End If

Not sure if I am approaching this in the right way but your help would be really appreciated.

Cheers
Jay

[Bob Phillips]

For i = LBound(sArr) To UBound(sArr)
Worksheets(sArr(i)).Visible = Not Worksheets(sArr(i)).Visible
Next i

[NewDaddy]

Hi xld

Thank you very much.
It works like a dream.

Sorry for taking a while but I did'nt know you responded.

Cheers
Jay