CODEWITHSUNDEEP
vbaexcel
Tim
Tim

Reputation: 19

VBA For Each worksheet only working on first sheet

I am creating a workbook with 17 sheets and each one has a product list that will change from month to month. If a product has errors, it shows up as CMB in the values but the product is still there. I was to delete the product rows. This code works on a sheet by sheet basis, but once I try to loop it, it does not work.

Sub wsLoop()
Dim ws as Worksheet
For Each ws In Worksheets
'Seeing if there are new products added to list
countcells = Range(Range("F8").End(xlDown).Offset(, -4), Range("A8").End(xlDown)).Cells.SpecialCells(xlCellTypeConstants).Count
'if no products added, then see if there in CMB in any row and delete that row
If countcells = 1 Then
If Not Range("E:E").Find("CMB") Is Nothing Then

Range(Range("E:E").Find("CMB"), Range("E8").End(xlDown)).Rows.EntireRow.Delete

End If
End If
Next ws
End Sub

Upvotes: 1

Views: 679

Answers (1)

devuxer
devuxer

Reputation: 42394

You have to actually get the range of the current worksheet. E.g., 

countcells = ws.Range(ws.Range("F8").End(xlDown).Offset(, -4), ws.Range("A8").End(xlDown)).Cells.SpecialCells(xlCellTypeConstants).Count

Otherwise, you will always be just grabbing ranges off the currently selected worksheet (which will generally be the first worksheet).

Note that you will need to repeat this for all instances of Range.

By the way, one thing you can do to make this easier is to use With:

With ws
    countcells = .Range(.Range("F8").End(xlDown).Offset(, -4), .Range("A8").End(xlDown)).Cells.SpecialCells(xlCellTypeConstants).Count
    'repeat for all lines
End With

With eliminates the need to repeat the name of the object over and over. You just type .property, and it automatically knows you mean ws.property.

Upvotes: 2

Related Questions