Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Selecting and Grouping Sheets

  1. #1
    Guest

    Default

    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

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Guest

    Default

    Hi Dave

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


  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  5. #5
    Guest

    Default

    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

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.




Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •