VBA; Copy and paste row on condition

[Das1987]

Hello,

I have tried to write some VBA to copy and paste rows of data based on a condition. So far I have only been able to copy the first cell. It would be appreciated if someone could point out where I am going wrong so that I know in future.

Sub CopytoOtherSht()
    Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter 1, "WISBECH ROADWAYS  CUSTOMERS"
    Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheets(2).Range("A" & Rows.Count).End(xlUp)(2)
    Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
End Sub

Many thanks in advance.

 

[davesexcel]

When pasting a range of data, you only need to paste it to the last available cell.
Example

Sub CopyFilteredData()

    Dim Rws As Long, Rng As Range


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

    Set Rng = Range(Cells(2, 1), Cells(Rws, 1))

    Range("A1").AutoFilter Field:=1, Criteria1:="WISBECH ROADWAYS  CUSTOMERS"
    Rng.Copy Destination:=Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Range("A1").AutoFilter

End Sub

 

[Das1987]

Thanks for that, but it still only copies Column A.

Not sure I explained well enough but what I wanted to do is copy and paste the entire row of data where column A matches the criteria.