Twist on "Copy and paste entire row to second sheet based on cell value"

[Dr. Demento]

I have successfully adapated Michael M's code (found here, Post #4) to meet my needs (thanks much, Michael!). However, rather than add the pasted data to the end of the data, I wish to overwrite the previously pasted data (paste data at A5 everytime the macro is run). I suspect it has to do with the statement ".End(xlUp).Row" in multiple locations - I'm enough of a noob that I can't figure out how to adapt the code to overwrite existing data.

Any help would be much appreciated!

Dr. D


Michael M's original code:

Sub As_Of_Analysis_Sorting()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("All Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("Non-As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If Range("P" & r).Value = "YES" Then
Rows(r).Copy Destination:=Sheets("As-Of Trades").Range("A" & lr2 + 1)
lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("P" & r).Value = "NO" Then
Rows(r).Copy Destination:=Sheets("Non-As-Of Trades").Range("A" & lr3 + 1)
lr3 = Sheets("Non-As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
End If
Range("A1").Select
Next r
End Sub

 

[Smitty]

Welcome to the Board!

Just use an explicit reference to A5:

Rows(r).Copy Destination:=Sheets("As-Of Trades").Range("A5")

HTH,

 

[Dr. Demento]

Thank you, Smitty!

It does paste in the desired cell, but it only copies the first record from "All Trades". Suggestions?

 

[Smitty]

It looks like it pastes all trades, but only leaves you with the last since you're overwriting each one in A5. Since you're looping from the bottom up that leaves you with the first trade, so it's doing what you're telling it to do.

If you're trying to overwrite existing data, but still paste sequential records from the source sheet, then you'd probably want to delete the contents of the destination sheets first, and still use the LR method (not the explicit A5 reference).

 

[Dr. Demento]

Not ideal (as I have headers), but when I attempt to use a Clear Range statement, I get undesireable results (it clears everything in the specified range but winds up pasting the data as if the data had not been erased). I must be putting it in the wrong place (I understand the syntax of the Clear statement); would you mind giving me a pointer as to where/how the Clear statement should be utilized?

Thanks!

 

[Smitty]

Are you working with a Table? If so you need to delete the DataBodyRange first.

Otherwise try deleting the previously used rows instead of just clearing.