Macro To Replace Existing Data With Newly Copied Data

[MarkStrong]

Hi guys,

I wasn't able to find this question in any of my searches so it would be great if you could help me out.

I want to create a macro that will copy a set of data (identified by common field, ie. Coffee Type in this example) and replace existing data in a separate worksheet.

I.e.:
Worksheet 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Coffee Type[/TD]
[TD]Temperature[/TD]
[TD]Size[/TD]
[/TR]
[TR]
[TD]Latte[/TD]
[TD]Medium[/TD]
[TD]Large[/TD]
[/TR]
</tbody>[/TABLE]

Copy data (A2:C2) and then paste in the following worksheet, if Latte is pre-existing then overwrite Latte data else paste a new row at the end.

Worksheet 2:

If Latte data is already there, find and replace over it (i.e. it would update Small to Large)


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Coffee Type[/TD]
[TD]Temperature[/TD]
[TD]Size[/TD]
[/TR]
[TR]
[TD]Latte[/TD]
[TD]Medium[/TD]
[TD]Small[/TD]
[/TR]
</tbody>[/TABLE]


Or if Latte is not there, paste at the end (in red):


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Coffee Type[/TD]
[TD]Temperature[/TD]
[TD]Size[/TD]
[/TR]
[TR]
[TD]Espresso shot[/TD]
[TD]Hot[/TD]
[TD]Small[/TD]
[/TR]
[TR]
[TD]Latte[/TD]
[TD]Medium[/TD]
[TD]Large[/TD]
[/TR]
</tbody>[/TABLE]



This has been doing my head in for a couple days so thank you for any help you could offer.

 

[skywriter]

Cross Posted.

 

[MarkStrong]

Quite right! I have removed cross post. Thanks skywriter.