Copy data on sheet2 to sheet1 base on column data.

[Tocix]

Please help me out. I want a vba that can automate this process that copy sheet2 to sheet1. For example on sheet2. Column D4 fruit column separate by a blank line with a new fruit with dates, purchase orders and quantity. There's six types of fruit that I need to keep track. What I want to do is to copy fruit types on column D all rows that are "apple" and copy data on column A,B and C and paste it to sheet1 on A9,B9 and C9 for Apple and D9,E9 and F9 for banana and so on. I'm sorry I'm trying to explain as best as I can.

Thank you very much.



A9 will always be apple
D9 will always be banana
G9 will always be grapes

example below

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]date[/TD]
[TD]PO[/TD]
[TD]qty[/TD]
[TD]date[/TD]
[TD]PO[/TD]
[TD]qty[/TD]
[TD]date[/TD]
[TD]PO[/TD]
[TD]qty[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3/18/2019[/TD]
[TD]25[/TD]
[TD]5789[/TD]
[TD]4/21/2019[/TD]
[TD]18[/TD]
[TD]3009[/TD]
[TD]4/25/2019[/TD]
[TD]12[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3/23/2019[/TD]
[TD] 60[/TD]
[TD]6090[/TD]
[TD]4/24/2019[/TD]
[TD]39[/TD]
[TD]5881[/TD]
[TD]4/28/2019[/TD]
[TD]99[/TD]
[TD]64[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]date[/TD]
[TD]PO[/TD]
[TD]Qty[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3/18/2019[/TD]
[TD]25[/TD]
[TD]5789[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3/23/2019[/TD]
[TD]60[/TD]
[TD]6090[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3/28/2019[/TD]
[TD]30[/TD]
[TD]3098[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4/21/2019[/TD]
[TD]18[/TD]
[TD]3009[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4/24/2019[/TD]
[TD]39[/TD]
[TD]5881[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4/24/2019[/TD]
[TD]68[/TD]
[TD]3068[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]4/25/2019[/TD]
[TD]12[/TD]
[TD]28[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4/28/2019[/TD]
[TD]99[/TD]
[TD]64[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



****** id="cke_pastebin" style="position: absolute; top: 312px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]4/28/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Try this

Sub copy_data()
  Dim sh2 As Worksheet, r As Range, j As Long, c As Range
  Set sh2 = Sheets("Sheet2")
  j = 1
  For Each r In sh2.Range("A2:C" & sh2.Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas
    Set c = Union(sh2.Range("A1:C1"), r)
    c.Copy Sheets("Sheet1").Cells(8, j)
    j = j + 3
  Next
End Sub

 

[Tocix]

I probably gave a bad example. Code gave me an error. Can you please take at a look at the file?

https://www.dropbox.com/s/9uyvmcypwd3o58h/Fruit Pruchase order.xlsx?dl=0

 

[DanteAmor]

What error message the code sends you and on which line the macro stops.

If your data is on sheet2 as your initial example, you will see the result on sheet1.

 

[Tocix]

Your code work on sample sheet, but it does not work on my real sheet. gives run-time error "1004" macros stop at c.Copy Sheets("Sheet1").Cells(8, j). I tried adjusting code with no success.

Thanks

 

[DanteAmor]

Your code work on sample sheet, but it does not work on my real sheet. gives run-time error "1004" macros stop at c.Copy Sheets("Sheet1").Cells(8, j). I tried adjusting code with no success.

Thanks

What else says the error?
Do you have the protected sheet, or merged cells or what is the real example?