Simple Copy/Paste VBA is copying the same data multiple times :(

[amandabstewart]

this works, BUT, when I run the macro again, it copies old data as new at the bottom. it's ok for it to overwright content in A2 on the destination page, but it takes the data like it is new and I get repeat data in the destination table.
any ideas? trying to delete either of the set pastecell parts causes the macro to die



Option Explicit

Sub CopyP2()

Dim PatientCol As Range
Dim Patient As Range
Dim PasteCell As Range

Set PatientCol = Sheet1.Range("A2:A99")

For Each Patient In PatientCol


If Sheet4.Range("A2") = "" Then

Set PasteCell = Sheet4.Range("A2")
Else

Set PasteCell = Sheet4.Range("A1").End(xlDown).Offset(1, 0)
End If

If Patient = "P2" Then Patient.EntireRow.Copy PasteCell

Next Patient

End Sub

 

[My Aswer Is This]

OK I do now see this line of code:
Sheet4.Range("A1").End(xlDown).Offset(1, 0)

 

[Fluff]

That is because he never said what sheet.

Actually it's
a) in the code the OP posted.
b) specified in post#5

 

[My Aswer Is This]

One important thing to do is try to read the code as much as possible.
Like where I said sheet(2)

You could change that to Sheet(4)
And in a lot of cases I think you should use sheet names like
Sheet named "Alpha"
The more you can it will help you in future to try to read and understand as much as you can.

 

[Fluff]

The OP is currently using the sheet codenames, which are far more reliable than either the sheet index that you used, or the sheet names.

 

[My Aswer Is This]

Actually if you look at my script you will see:
Dim s As Variant
C = 1 ' Column Number Modify this to your need
s = "P2" 'Search Value Modify to your need

So this means search column 1
For the value "P2"

so next time you could change P2 to P5 or what ever and change 1 to 5 if we want to search column 5

I try to make my scripts easy row read and be modified easily.