Macro to copy rows based on value

[Justair07]

Hello,

I'm looking for a macro that can search mutiple tables on the same page and then paste any data with with a value greater than one to a new table. Also i would like the macro to -1 from the data thats being copied. Please see attchement for an example. I tried doing this in a filter put the filter changes all the rows in the worksheet and i need it all presented together.

Please Help!!

I have been trying all day to make this work

[JieJenn]

If you get rid of the Merge you can just use a regular formula and I don't see macro is necessary.

=SUMIF($A$2:$A$27,E14,$B$2:$B$27)-1

[Justair07]

Getting rid of the merge is no problem, thank you for the tip but is it possible to some how have the name follow over to the table as well. this way i can avoid retyping all the name every time i do the report.