Group sheets without defined array?

[sweeneytime]

Hi all,

Trying to group sheets with a macro.

I was using an array with the sheet names defined. But I want the code to be able to select new sheets if added. Selecting sheets based on data in "A5".

I adapted code that looped and selected sheets correctly. But it won't group, just selects each sheet individually and moves on.

Any suggestions?

Thanks,
Alan

Sub SelectCC()
    Dim ws As Worksheet
    
    'Select sheets with "INCOME" in A5
    'Should only be the Cost Centres
    For Each ws In ActiveWorkbook.Worksheets
            If ws.Range("A5").Text = ("INCOME") Then
                ws.Select
            End If
    Next ws
    

End Sub

 

[G2K]

Thanks a lot Peter.

I was not aware about this property. also,in the meantime i checked bleow link.
http://www.tushar-mehta.com/excel/vba/multiple_sheets.htm

Thanks again.

 

[G2K]

I'll try.

The Replace parameter is Boolean (True or False) with the default being True.
It determines whether to replace the existing sheet selection (if True) or not (if False). If not, then it adds to the existing selection.

The problem was, by omitting the parameter it was using the default value of True and so every time you came to a new sheet that met your criteria, the sheet was selected but the previous one was de-selected.

When I Dim the boolean bStarted, it takes the default value of False. So the first sheet I come to that meets the criteria
Replace:=Not bStarted is really
Replace:=Not False
Replace:=True

This ensures that if your code starts on a sheet that does not meet your criteria, it will be de-selected. If you do start on a sheet that meets your criteria it will also be de-selected but it replaced in the selection by itself - so no problem.

Having done this first sheet, I set bStarted to True so that from then on
Replace:=Not bStarted
Replace:=Not True
Replace:=False

so the sheet selection just gets added to.

Hope that made some sense.

Thanks Peter.

i was not aware about this property. I learned a new property of VBA today.also, in the meantime i checked below link.
http://www.tushar-mehta.com/excel/vba/multiple_sheets.htm

Many Thanks