Macro Help

[simplymidori]

Run this macro on ALL worksheets except for worksheet labeled "TRACKER"

If column B contains "Total" insert 2 rows below.


Thanks for your help.

 

[JE McGimpsey]

one way:

Public Sub InsertTwoRowsAfterTotal()
Dim ws As Worksheet
Dim rFound As Range
Dim sFoundFirst As String

For Each ws In ActiveWorkbook.Worksheets
With ws
If .Name <> "TRACKER" Then
With .Columns(2).Cells
Set rFound = .Find( _
What:="Total", _
after:=.Item(.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
Searchdirection:=xlNext, _
MatchCase:=False)
If Not rFound Is Nothing Then
sFoundFirst = rFound.Address
Do
rFound.Offset(1, 0).Resize( _
2, 1).EntireRow.Insert _
Shift:=xlShiftDown
Set rFound = .FindNext(after:=rFound)
Loop Until rFound.Address = sFoundFirst
Set rFound = Nothing
End If
End With
End If
End With
Next ws
End Sub