Row copy macro out of control

[scoha]

I cannot figure out why my row copy macro goes out of control and copies the same row 100 times to the point it crashes. My code is :

Private Sub Worksheet_Activate()

Dim i As Long
Dim LR As Long
   
LR = Sheets("OpenIssues").Range("B" & Rows.Count).End(xlUp).Row

For i = 7 To LR

 If Sheets("OpenIssues").Range("D" & i) = "Closed" And Sheets("OpenIssues").Range("L" & i) > 0 Then
    Sheets("OpenIssues").Rows(i).Copy
    Sheets("ClosedIssues").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 End If

Next i
    
For i = 7 To LR
    
 If Sheets("OpenIssues").Range("D" & i) = "Closed" And Sheets("OpenIssues").Range("L" & i) > 0 Then
    Sheets("OpenIssues").Rows(i).EntireRow.Delete
 End If

Next i
Exit Sub

It fails on the

Sheets("OpenIssues").Rows(i).Copy

line.

Any ideas?

 

[bensonsearch]

What is i set to to start with ? Try setting to 7 ( or whatever start number) manually before your loop. Also I wouldn't have another if statement inside a second loop of the same conditions. Use a else if inside the same loop.

Try hitting debug (f8) and going through the code. When it gets to that line hover over i to see it's value. This will also show you how the data and loop are working

 

[jim may]

Omit the "s" in your line:

Sheets("OpenIssues").Rows(i).Copy

making it

Sheets("OpenIssues").Row(i).Copy

 

[jasonb75]

Your code is fine, the only thing you need to make it work is to disable events while the code runs.

Private Sub Worksheet_Activate()
Application.EnableEvents = False
 
Dim i As Long
Dim LR As Long
 
LR = Sheets("OpenIssues").Range("B" & Rows.Count).End(xlUp).Row
 
For i = 7 To LR
 
 If Sheets("OpenIssues").Range("D" & i) = "Closed" And Sheets("OpenIssues").Range("L" & i) > 0 Then
    Sheets("OpenIssues").Rows(i).Copy
    Sheets("ClosedIssues").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 End If
 
Next i
 
For i = 7 To LR
 
 If Sheets("OpenIssues").Range("D" & i) = "Closed" And Sheets("OpenIssues").Range("L" & i) > 0 Then
    Sheets("OpenIssues").Rows(i).EntireRow.Delete
 End If
 
Next i
Application.EnableEvents = True
End Sub

Although you could trim it down a little to something like

Private Sub Worksheet_Activate()
    Application.EnableEvents = False
Dim i As Long, LR As Long
With Sheets("OpenIssues")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 7 Step -1
    If .Cells(i, "D") = "Closed" And .Cells(i, "L") > 0 Then
       .Rows(i).Copy
    Sheets("ClosedIssues").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
       .Rows(i).EntireRow.Delete
    End If
Next i
End With
    Application.EnableEvents = True
End Sub