If Cell = peaches, Then Copy Entire Row Into A New Sheet

[EYHermans]

Hello,

I am new here and for my daily work I need to copy data from one worksheet to another.
Till now I am using the filter option and copy everything manually, but as it takes some time I would like to automate this.

I already found this tread and modified it a bit to something that looks like my own datasheet, but I figured out that in this VBA code only limited data is copied. If I start of with "peaches" only, row A / C / E / G / I / K are copied to Sheet1...

Can someone help me with that? And is it also possible that - when re entering the "Command Button", first all data of Sheet 1/ Sheet 2/ Sheet 3 is removed and then copied? This in order to not have doubles listed.

Thanks for you help in advance!

Kind regards,
Eva

PS: See here the worksheet

[CAntosh]

If I'm understanding your needs correctly, you don't need VBA for this. Put the formula below in A2 of "sheet1". Confirm it with Ctrl + Shift + Enter instead of the regular Enter. Then, fill right through column D, then fill down through as many rows as you think you'll need for "peaches".

=IFERROR(INDEX(Input!A$2:A$100,SMALL(IF(Input!$B$2:$B$100="peaches",ROW(Input!$B$2:$B$100)),ROW(1:1))-1),"")

This formula covers 99 entries. If you think you'll need more, change the "100"s in the formula to something bigger. You can do the same with sheets 2 and 3, just change the part of the formula that says "peaches" to whatever you're filtering for on that sheet. If you fill down through empty rows, the sheets should update automatically as you add new entries into "Input".

See attachment for clarity...

[jolivanes]

Of met een macro.

Please Login or Register  to view this content.

[EYHermans]

Thank you guys very much! Actually I like the formula the most as I do understand that the best myself .
However, my sheet is a bit slow now, I can easily adjust it myself.

Thanks for you help!!