VBA PasteSpecial

[kgkev]

I'm trying to do a paste special but can't get the syntax right (I think

Sub UpdateSubReport()

Sheets("Stop List").Cells.Delete

Dim item As Range
For Each item In Sheets("Debt Report").Range("O11", Sheets("Debt Report").Range("O65000").End(xlUp))
    If item.Value = 1 Then
        item.EntireColumn.Resize(, 10).Copy
        Sheets("Stop List").Range("A65000").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    End If
Next item
End Sub

just trying to copy all rows that have a 1 in the O column.

It wo

 

[RoryA]

You can only copy an entire column to a cell in row 1.

 

[Domski]

Have you thought about using Autofilter?

Dom

 

[texasalynn]

not sure you have the correct area for copy - maybe this

        item.EntireRow.Resize(, 10).Copy
        LR = Sheets("Stop List").Cells(Rows.Count, 1).End(xlUp).Row
        Sheets("Stop List").Range("A" & LR + 1).PasteSpecial Paste:=xlPasteValues