VBA copy and paste rows

[Newbie76]

Hi, I'm new to VBA, I have a code which is below, the code works great if my data start in column A, but my data start in column D row 2. How do I change it for column D?

Private Sub CommandButton1_Click()

For i = 2 To 25

If Worksheets("Today").Cells(i, 9).Value > 2 Then

Worksheets("Today").Rows(i).Copy

Worksheets("Completed").Activate

b = Worksheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("Completed").Cells(b + 1, 1).Select

ActiveSheet.Paste
Worksheets("Today").Rows(i).ClearContents
End If
Next
End sub

 

[mumps]

Do columns A, B and C contain any headers in row 1 or any data or are they blank?

 

[Newbie76]

Do columns A, B and C contain any headers in row 1 or any data or are they blank?

Row 1 has headers

 

[mumps]

Try:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim lCol As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Today")
    Set desWS = Sheets("Completed")
    With srcWS
        lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Range("I1").AutoFilter 9, ">2"
        .AutoFilter.Range.Offset(1, 3).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .Range("I1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[Newbie76]

Thank you so much, this works perfectly.

 

[mumps]

You are very welcome.