VBA to find a variable value and copy found cell and cell above it to a new sheet and find next value and do the same

[ant_85ir]

Hi,

I have an excel sheet with many entries. At the bottom of excel sheet i like to have a cell to enter any value and based on that value, a macro finds that value on the sheet, copy found cell and the cell above it and the cell on the very left column on the row above found cell into a new sheet. Thenthe macro to find the next value on the sheet, again copy found value and cell above it and the cell on the very left column on the row above found cell and copy them on the new sheet.
Example: i enter "car" as a vlue in a predefined cell. The macro finds the value on a particular cell containing that value, lets say G499, then the macro copies G499 and G498 and A498 to a 2nd sheet on the following cells respectively: A1, A2, A3.
Then the macro should come back to the original sheet with many entries find "car" again and it finds the next cell containing "Car" lets say H510, then the macro copies H510 and H509 and A509 to to a 2nd sheet on the following cells B1, B2, B3.
It keeps doing that until it finds all "car" entries on the original sheet and then it gives a message "All entries copied".

Thanks very much