Looping & Selection + Loop

[Benzula]

Okay guys. I got a simple question, and a Complex question.

Simple. I need to run this Macro on every sheet in a workbook. I have it so it works on the current active sheet.

So I assume a simple Loop with this Macro would allow me to do it, how would I structure so that it goes to the next tab regardless of what it is called.

Here is the current code

Sub Send_Selection_Or_ActiveSheet_with_MailEnvelope()
'Working in Excel 2002-2013
    Dim Sendrng As Range
    
    On Error GoTo StopMacro


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    'Note: if the selection is one cell it will send the whole worksheet
    Set Sendrng = Selection


    'Create the mail and send it
    With Sendrng


        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope


            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = "Good Afternoon, " & vbNewLine & vbNewLine & "Below you will find your accounts. 


            With .Item
                .To = ActiveSheet.Name
                .CC = ""
                .BCC = ""
                .Subject = "Please find below a list of all of your accounts."
                .Send
            End With


        End With
    End With


StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False


End Sub

Okay! Now for the the more complex problem. Below is a sample (No Real Data)

If I have multiple rows of customers, they are all sorted by smallest to largest on MDM - Available.

What i need is to have the macro above run, but select the header +all rows that have negative MDM - Available. So if there were 3 more in addition to the one below with -10, -3 , 7. It would only select to send the first one + the ones with -10 and -3 with the header.

This is variable number of records per tab. So tab 2 could have 10 records with negative MDM - Available, and tab 3 could have 3.

Also If there are no records with negative numbers, I would want it to SKIP.

Again would need this to loop to each tab.

[TABLE="class: grid, width: 1837"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SalesForce ID[/TD]
[TD]Customer[/TD]
[TD]Compliance Notes[/TD]
[TD]Open Opportunities[/TD]
[TD]Open Opp Device Level[/TD]
[TD]Account Executive - Account[/TD]
[TD]MDM - Active[/TD]
[TD]MDM - Purchased[/TD]
[TD]MDM- Available[/TD]
[TD]AW Email Client[/TD]
[TD]AW CL[/TD]
[TD]AW BR[/TD]
[TD]AW WS[/TD]
[TD]SF Account Status[/TD]
[TD]Hosting Model[/TD]
[/TR]
[TR]
[TD]123456789dsa[/TD]
[TD]ABC Corp[/TD]
[TD] [/TD]
[TD]TRUE-UP - ABC Corp[/TD]
[TD]15[/TD]
[TD]John Rhodes[/TD]
[TD]86[/TD]
[TD]70[/TD]
[TD]-16[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Active[/TD]
[TD]AW SaaS[/TD]
[/TR]
</tbody>[/TABLE]

 

[Benzula]

So stepping through the code shows me that it sends 4 emails based on 1 sheet all at once. It doesn't loop through the code 4 times.

So I think maybe in the previous bit of the macro it basically counts the number of sheets. and then since I'm on the Active sheet it sends that one 4 times.

I tried creating another sheet just as a test, and it sends 5 now to me. So the number of emails is based off the number of tabs there.

I don't see it change to the next worksheet.

 

[Peter Thompson]

That is strange it should change worksheets on the next worksheet command. Can you post your complete code.

 

[par60056]

couple things:
1) using worksheet as a variable is going to be confusing. shorten it to WS.

For Each Worksheet In Worksheets

becomes

For Each ws In Worksheets

2) your loop will execute for each worksheet, but you need to activate it. Right after the "For Each" you need the line:

ws.Activate