Button for a For Loop

[Gryder]

Hello,

I have some code that works but not exactly how I want it to work. When I press my transfer button, it sends the data to the other two sheets (Week, Month) but only one row at time, sometimes more than one row. I have to keep pressing the button until all data is moved over.

I want my code to send all data from the rows to the other sheets with once press. I believe I need a loop but have searched and tried different code with no success. Code is below. Any help is appreciated. Garrett


Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

Dim i As Long
Dim Lastrow As Long

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 4 To Lastrow

If Cells(i, 7) = "Not Due" And Cells(i, 9) = "Week" Then
Rows(i).Copy Destination:=Sheets("Week").Range("A1048576").End(xlUp).Offset(1, 0)
Rows(i).Delete

ElseIf Cells(i, 7) = "Not Due" And Cells(i, 9) = "Month" Then
Rows(i).Copy Destination:=Sheets("Month").Range("A1048576").End(xlUp).Offset(1, 0)
Rows(i).Delete

End If
Next i
Application.ScreenUpdating = True

End Sub

 

[Joe4]

When deleting or inserting rows, it is best to work backwards through your range, as you may be moving data into a range you already checked (when deleting).

So, change your loop line to this:

For i = Lastrow to 4 Step -1

 

[Gryder]

Thank you Joe4! That worked exactly as I wanted it to.

 

[Joe4]

You are welcome.
Glad I was able to help!

Just remember, whenever inserting or deleting rows, it is always best to loop through your rows backwards.