CODEWITHSUNDEEP
vbaexcel
Anoni Moose
Anoni Moose

Reputation: 35

VBA Macro won't loop through sheets in workbook

Sub CullData():

Dim ws As Worksheet
Dim Counter As Integer
Dim obsoleterows As Range

For Each ws In ThisWorkbook.Worksheets

For Counter = 1 To 40 Step 2
    ws.Range("P2").Offset(Counter - 1, 0) = ws.Range("I1").Offset(Counter - 1, 0)
    ws.Range("Q2").Offset(Counter - 1, 0) = ws.Range("K1").Offset(Counter - 1, 0)

    If obsoleterows Is Nothing Then
        Set obsoleterows = Rows(Counter)
    Else
        Set obsoleterows = Union(obsoleterows, Rows(Counter))
    End If

Next Counter
obsoleterows.Delete
Set obsoleterows = Nothing

Next ws

End Sub

For some reason the loop runs but doesn't move from one sheet to the next - I think it just runs the same code on the first sheet in the workbook about ten times (as I have 10 sheets). If I remove the loop it runs once and closes so the code recognizes the loop, it just doesn't follow it. I've tried variations on the above but I get the same issue each time.

Upvotes: 1

Views: 303

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Not all of the range references in your loop are scoped to ws

If obsoleterows Is Nothing Then
    Set obsoleterows = Rows(Counter)
Else
    Set obsoleterows = Union(obsoleterows, Rows(Counter))
End If

should be:

If obsoleterows Is Nothing Then
    Set obsoleterows = ws.Rows(Counter)
Else
    Set obsoleterows = Union(obsoleterows, ws.Rows(Counter))
End If

....otherwise it just defaults to the ActiveSheet.

obsoleterows.Delete

would be safer as 

If Not obsoleterows Is Nothing Then obsoleterows.Delete

Upvotes: 1

Related Questions