combining specific column of certain worksheets, adding duplicates on combo worksheet

I am having a problem creating the vba code for the attached worksheet. I need to combine the tabs titled "copper & fiber", "av", "catv", "cctv", and "ac" into the tab titled "vendor pricing. The tab needs to be clear each time before the macro is run, leaving the heading information. I also need to locate duplicates and add those totals and then remove the remaining lines. Anything that has a zero unit doesn't need to be pulled over to vendor pricing. Is this possible? The only columns I need pulled from each of the aforementioned tabs is part #, description, qty, unit, unit price, and ext. price. On the vendor tab is a column titled delivery. This needs to remain on the vendor pricing tab. This is also a section at the bottom on each page that lists total hours, man hours, etc., this doesn't need to be pulled over to the vendor pricing page. Any help you could offer would be fantastic!

Welcome to the forum.

No attachment. Zip if necessary.