How can I have my code scan through my data and copy certain selected rows of data and paste it to a different sheet?

Question

So basically I have a bunch of data in wksht1 that I want to scan through to select all the rows that adhere to a certain if then statement (see code below) and then copies them to pastesht. I have my code so far but it gives me an error at line 20 and I can't figure out what I need to fix. Please help and thanks in advance!

 1   Sub Try()
 2   Dim wksht1 As Worksheet, pastesht As Worksheet
 3   Dim LastRowinpastesht As Long
 4   
 5   With ThisWorkbook
 6        Set wksht1 = Sheets("AAA")
 7        Set pastesht = Sheets("PPP")
 8        LastRowinpastesht = pastesht.Cells(Rows.Count, 1).End(xlUp).Row + 1
 9    End With
10
11   With wksht1
12       Last1 = Cells(Rows.Count, "B").End(xlUp).Row
13       For p = Last1 To 1 Step -1
14       If (Cells(p, "F").Text) = "SSSS" And (Cells(p, "U").Value) <= 5 And (Cells(p, "U").Interior.ColorIndex) = xlNone Then
15       Cells(p, "A").EntireRow.Select
16       Selection.Copy
17       End If
18
19    With pastesht
20       Cells(LastRowinpastesht, 1).Paste
21       Application.CutCopyMode = False
22    End With
23       Next p
24    End With
25    End Sub

Try to qualify it. `.Cells(LastRowinpastesht,1).Paste`. Also, saw some errors. Lemme try editing it. :) — WGS, Jan 30 '14 at 17:21
and also change `Last1 = Cells(Rows.Count, "B").End(xlUp).Row` to `Last1 = .Cells(.Rows.Count, "B").End(xlUp).Row` and every statement `Cells(...)` to `.Cells(...)` — Dmitry Pavliv, Jan 30 '14 at 17:22
I tried both of those but its still giving me the error in the same place. — SRoy, Jan 30 '14 at 17:27

score 1 · Accepted Answer · answered Jan 30 '14 at 17:32

Try this:

Sub TryMod()

    Dim WS1 As Worksheet, PasteSht As Worksheet
    Dim PShtNRow As Long, WS1LRow As Long

    With ThisWorkbook
        Set WS1 = .Sheets("AAA")
        Set PasteSht = .Sheets("PPP")
    End With

    With WS1
        WS1LRow = .Cells(Rows.Count, "B").End(xlUp).Row
        For Iter = WS1LRow to 1 Step -1
            PShtNRow = PasteSht.Cells(PasteSht.Rows.Count, 1).End(xlUp).Row + 1
            If .Cells(Iter, "F").Value = "SSSS" And .Cells(Iter, "U").Value <= 5 And .Cells(Iter, "U").Interior.ColorIndex = xlNone Then
                .Rows(Iter).Copy PasteSht.Rows(PShtNRow)
            End If
        Next Iter
    End With

    Application.CutCopyMode = False

End Sub

The main issue is you have to qualify your properties and methods when you're using With. You can't got With WS1 then use just Cells. It has to be .Cells. Otherwise, Excel won't know which sheet's Cells you're referring to, among other possible errors that can happen.

Next is, with regards to For loops and especially copying to the next empty row in another sheet, you have to remember to put the check for the next empty row inside the loop. If you set it at the beginning, it will only take in one value and overwrite the row that corresponds to that value again and again.

Now, the above is hacked together really quickly so kindly test it and let us know of the results. :)

+1 in fact range doesn't support Paste property, but your code solves this problem:) — Dmitry Pavliv, Jan 30 '14 at 17:39
It actually worked perfectly and thank you so much for explaining your code as well so I can actually understand where I went wrong. Thanks so much! — SRoy, Jan 30 '14 at 17:39
@simoco: I don't understand that inane part about Range objects really. I mean, of the many things, really, Microsoft? :D Thanks, OP. Good luck. :) — WGS, Jan 30 '14 at 17:40

score 1 · Answer 2 · answered Jan 30 '14 at 17:36

the range object doesnt support the use of Paste

replace the line in error with Cells(LastRowinpastesht, 1).PasteSpecial xlPasteAll

that should clear that up. Also there are other errors to check for just fyi, I dont think Last1 was ever declared anywhere to start with.

How can I have my code scan through my data and copy certain selected rows of data and paste it to a different sheet?

2 Answers2