Selecting and Grouping Sheets

G

Guest

Guest
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
Hi Dave

Does not select any. Ran with F8 and noted it selected all lines except "wsSheet.Select False"
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top