Run Macro on Each Sheet of Active Workbook

[zach9208]

The following code does not run across all of my worksheets. It seems to just run the same code on the first tab multiple times. Can someone help me fix this? My best guess is that that the ActiveCell member is causing this issue, but I do not know how to fix this.

Sub DeleteRowsfromEachSheet()

Dim wk As Worksheet
For Each wk In ThisWorkbook.Worksheets
Dim Counter As Long


Range("B1").Select ' Start at B1
For Counter = 1 To 100
If ActiveCell.Value Like "*Allocated Operating Exp OE Comp and Other Excl Stock Comp*" Then
ActiveCell.EntireRow.Delete ' Delete Row
Else
ActiveCell.Offset(1, 0).Select  ' Move down a row
End If
Next Counter
Next wk


End Sub

 

[Joe4]

Looping through the list of worksheets does not actually select or activate them.
So you either need to select each sheet before you start working on them, like this:

For Each wk In ThisWorkbook.Worksheets
    wk.Activate

or include the worksheet reference in each range reference, i.e.

For Each wk In ThisWorkbook.Worksheets
    wk.Range("B1")

Also, you want to avoid using ActiveCell in your code if possible.
And when deleting rows in a range, it is always best to loop through your range backwards.

This code should do what you want:

Sub DeleteRowsfromEachSheet()

    Dim wk As Worksheet
    Dim Counter As Long
    
    For Each wk In ThisWorkbook.Worksheets
        For Counter = 100 To 1 Step -1
            If wk.Range("B" & Counter).Value Like "*Allocated Operating Exp OE Comp and Other Excl Stock Comp*" Then
                wk.Range("B" & Counter).EntireRow.Delete ' Delete Row
            End If
        Next Counter
    Next wk

End Sub

 

[zach9208]

Thanks, Joe. Your code is exactly what I was looking for! Thanks for the explanation as well.

Looping through the list of worksheets does not actually select or activate them.
So you either need to select each sheet before you start working on them, like this:

For Each wk In ThisWorkbook.Worksheets
    wk.Activate

or include the worksheet reference in each range reference, i.e.

For Each wk In ThisWorkbook.Worksheets
    wk.Range("B1")

Also, you want to avoid using ActiveCell in your code if possible.
And when deleting rows in a range, it is always best to loop through your range backwards.

This code should do what you want:

Sub DeleteRowsfromEachSheet()

    Dim wk As Worksheet
    Dim Counter As Long
    
    For Each wk In ThisWorkbook.Worksheets
        For Counter = 100 To 1 Step -1
            If wk.Range("B" & Counter).Value Like "*Allocated Operating Exp OE Comp and Other Excl Stock Comp*" Then
                wk.Range("B" & Counter).EntireRow.Delete ' Delete Row
            End If
        Next Counter
    Next wk

End Sub

 

[Joe4]

You are welcome. Glad I was able to help!