VBA: Copy specific cells to another worksheet

[FatWhale38]

Dear all

I am having trouble trying to put together a macro.

In Sheet1, I have my data table. I put a simplified version below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Gender[/TD]
[TD]Breed[/TD]
[TD]City Council[/TD]
[TD]Microchip Number
[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]Dog[/TD]
[TD]Council A[/TD]
[TD]1030999876287540[/TD]
[TD]Adopted[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]Dog[/TD]
[TD]Council C[/TD]
[TD]4425588004585700[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]Cat[/TD]
[TD]Council B[/TD]
[TD]8735364841765360[/TD]
[TD]Adopted[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]Cat[/TD]
[TD]Council A[/TD]
[TD]3351709516296290[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If the animal has been adopted, I need to move the details to Sheet2.

I need to make 3 rows for each entry (one for the family adopting the animal, one for the council and one for our records).

I have to copy the relevant details in to specific cells in Sheet2 ie:
- microchip number to cell K2 (through till K4)
- Council details in L2 (through till L4)
etc.

And then repeat for the next adopted animal.

How do I script the macro to save me from having to copy paste every single row manually?

Many thanks in advance!

 

[FatWhale38]

Another thought:

Would it be possible to have in another column, defining say M2 and the macro copies the C2 (ie the council details) to M2 in Sheet2?

Thanks again!