select a column range based on a condition and copy to another sheet

I need help with a simple macro. I want to look for a number in a column range.
Then i want to select all cells above and below that cell until I encounter five consecutive zero value cells. Of the five cells with zero, the first encountered cell with zero at top and bottom has to be included in the selection range as well. I want to copy the range to next sheet.
So far example in the given data on sheet1, I would look for cell value 500 and then select range A6:A25 to be copied to sheet 2 with first value in cell D3. Many Thanks.

A1 94
A2 0
A3 0
A4 0
A5 0
A6 0
A7 68
A8 0
A9 0
A10 0
A11 9
A12 33
A13 500
A14 88
A15 70
A16 0
A17 36
A18 0
A19 36
A20 0
A21 0
A22 0
A23 74
A24 60
A25 0
A26 0
A27 0
A28 0
A29 0
A30 86
A31 10

You could use the following code.

Please Login or Register  to view this content.

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”