VBA Macro: Copy data from workbook one and paste it to the other workbook

Question

Hello,

I am creating a macro that would copy the data from one worksheet and paste it to the other worksheet.

worksheet 1 consists of some data in it. Below is the snapshot of worksheet1.

worksheet 2 consists of two tables. Below is the snapshot of worksheet2

I am copying the data from the worksheet 1 to worksheet 2  based on the country. If the country is US then it is pasted in the table 1 of worksheet2 and if the country is CA then it is pasted in table 2 of worksheet2.

I am able to copy and paste the data.  The problem where i am facing is that table 1 already consists of some data and when i paste the data in it then it gets added up with the existing data , so how do i remove the already existing data from table 1 before pasting it and the table is to be formatted based on the new copied data.

For example:  if i want to copy only US data from worksheet one to the other worksheet table1 .

As there is only 3 rows of data with country code US in worksheet 1. So it will copy only those 3 rows and paste it to the other worksheet in table1. But table1 already consists of some data in it ,so it should remove the existing data and then paste it and  table1 should contain only those 3 rows of data which it copied.

Can anyone help me with this ?

Regards

Wither

Answer 1

re:  "should remove the existing data" (from Table)

This removes all data from the Table...

  Worksheets(2).ListObjects(1).DataBodyRange.ClearContents

This copies data to the Table...

  Worksheets(1).Range("J10:M12").Copy Destination:=Worksheets(2).ListObjects(1).DataBodyRange.Rows("1:3")

End Sub

[edited above to change worksheets used]

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 2

Hi James Cone,

Thank you it works ! (:-) (:-)

Regards

Wither