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

amandabstewart

New Member
Joined
Aug 4, 2014
Messages
40
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,916
Office Version
  1. 2013
Platform
  1. Windows
OK I do now see this line of code:
Sheet4.Range("A1").End(xlDown).Offset(1, 0)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,916
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,649
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,916
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,143,690
Messages
5,720,311
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top