Selecting and Grouping Sheets

Hi

I have a workbook with 50 sheets. About 40 sheets are edited three times each day (over 24hrs that is) and then printed out. As each sheet is edited the character "M" is placed in cell B2 for filtering in a mail merge. The present method is to select the edited sheets grouping them with Ctrl, for the printouts. The 40 sheets vary each day and the editing is done when the info becomes available, so the sequence in stepping through the sheets is never the same. We would like to include in the macro, which also does other tasks, a loop which examines each sheet for the character "M" grouping these sheets and then printout. Anyone an idea on a loop to do this ?

Eugene Robinson
Rhos on Sea, Wales

[Dave Hawley]

Hi Eugene

If I have understood you try:


Sub GroupAllM()
Dim wsSheet As Worksheet

For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.Range("A1") = "M" Then
wsSheet.Select False
End If
Next wsSheet
End Sub

Hi Dave

Does not select any. Ran with F8 and noted it selected all lines except "wsSheet.Select False"

[Dave Hawley]

Then you either do not have the letter "M" in cell A1 or it's not a Captital "M". To stop case sensitivity use:

Option Compare Text
Sub GroupAllM()
Dim wsSheet As Worksheet

For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.Range("A1") = "M" Then
wsSheet.Select False
End If
Next wsSheet
End Sub

Hi dave
Double checked case sensitivity and "M" being in B2 (changed A1 to B2). This code appears to be the same as the first apart from the "Option Compare Text" ?

Regards
Eugene

[Dave Hawley]

Hi

It works just fine for me. That is it groups all sheets in the Workbook that have an "M" in cell A1. If you like to email me I'll send you the working example.