Run same routine in several worksheets

Monaco

New Member
Joined
Nov 29, 2016
Messages
11
I am trying to run the same routine in multiple worksheets. I have used many of the suggestions I found here and on other sites but it is still not working. I am trying to get the routine to identify where Cell C94 (on several worksheets) is greater than or equal to zero and send an email to notify managers. It does it fine until it runs into one sheet that meets the criteria, then stops. I need it to continue because there are other sheets that meet the criteria. In my email sub routine do I need to somehow send it back to the calculate routine after it sends and email?

Option Explicit
Sub Activate_Workbook_Never_Fail()
'Activate Workbook
Workbooks("The Never Fail Template.xlsm").Activate
End Sub
Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double
Dim Sheet As String
Dim Current As Worksheet
' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
If Sheet.Range("C94").Value <> "0" Then
Call Mail_small_Text_Outlook
' This line displays the worksheet name in a message box.
MsgBox Current.Name
Next
End Sub
Sub Mail_small_Text_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim rng As Range
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Auto Generated from the Never Fail Template" & vbNewLine & _
" " & vbNewLine & _
"The Direct Cite/Reimbursable Check is Out of Balance For Tab " & Range("C1").Value & vbNewLine & _
"Out of Balance by " & Range("C94").Value

On Error Resume Next
With OutMail
.To = "lawrence.monaco@navy.mil"
.Subject = "Out of Balance For Tab " & Range("C1").Value
.Body = strbody
'.Attachments.Add ("C:\test.txt")
.display

Application.Wait (Now + TimeValue("0:00:03"))
Application.SendKeys "%s"
MsgBox "Notification of Out of Balance Status Sent to Larry Monaco and Linda Robertson"

End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try this:

Code:
[COLOR=#333333]For Each Current In Worksheets[/COLOR]
[COLOR=#333333]If [/COLOR][COLOR=#333333]Current[/COLOR][COLOR=#333333].Range("C94").Value <> 0 Then[/COLOR]
[COLOR=#333333]Mail_small_Text_Outlook[/COLOR]
[COLOR=#333333]' This line displays the worksheet name in a message box.[/COLOR]
[COLOR=#333333]MsgBox Current.Name
[/COLOR]End If
[COLOR=#333333]Next[/COLOR]
 
Upvote 0
thank you but it tells me "Variable not defined" and points to Current.

How do I properly define that?
 
Upvote 0
In your Sub WorksheetLoop(), there is the following code (which, by the way, has syntax error, like missing "End If". I don't know why it ran at all.). Replace it with the code I posted above and see what happens.

Code:
[COLOR=#333333]For Each Current In Worksheets[/COLOR]
[COLOR=#333333]If Sheet.Range("C94").Value <> "0" Then[/COLOR]
[COLOR=#333333]Call Mail_small_Text_Outlook[/COLOR]
[COLOR=#333333]' This line displays the worksheet name in a message box.[/COLOR]
[COLOR=#333333]MsgBox Current.Name[/COLOR]
[COLOR=#333333]Next[/COLOR]
 
Upvote 0
Yes I replaced it with the code you recommended and it will run and send the first email when criteria is met on one of the sheets. When it comes back around, it gets stuck on Current "Variable not defined" and the macro stops.
 
Upvote 0
Put a break at this line - MsgBox Current.Name - and monitor the values of Current.Name and Current.Parent.Name. Once the code pauses at the line, press F8 to step through the code and see if the two names are what they should be.

"For Each Current In Worksheets", what workbook is the Current in? Try fully qualifying Worksheets (adding the workbook, something like ThisWorkbook.Sheets).
 
Upvote 0
It works! I used your recommended code, made Dim current As Variant and it is running through the sheets and sending emails.

Can't thank you enough - this was really important to me.
 
Upvote 0
So, unfortunately I did discover something - it runs through my sheets and correctly identifies where C94 is <>0 and generates an email in each case. But what is happening is that in the email it is leaving the text from the first instance. Stepping through the code I definitely see that is has moved on to another sheet when it generates the next email.

Do I need to clear the text from the email routine after it runs each time? Do you know how I could do that?
 
Upvote 0
After further testing I believe what I need to do is make sure that as the sub routine runs through each sheet it actually activates the sheet it is in. I have tried a number of activate statements but none seem to work. Here is the code:

For Each current In Worksheets
If current.Range("C94").Value <> 0 Then
Mail_small_Text_Outlook
MsgBox current.Name
End If
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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