Combine worksheets into a master sheet

Morning,

I am a newbie to Excel in many ways

I have a question you can answer, I'm sure

I am trying to 'automate' an excel file for project estimating
I don't even know enough about Excel to know which function I should use

HERE IS THE SCENARIO

There are multiple sheets.
Each sheet is used for a specific assembly of building components (boxes, pipe, wire, circuit breakers, etc.)
Not every assembly will be needed for a given project, some will have a quantity value of zero
IE: you may need only 2 types of boxes, but there are 10 different types listed.

At the bottom of each sheet is a summary list using the SUMIF function
I am using VLOOKUP to populate the remaining data columns from a Master Stock List for catalog #'s, etc.


HERE IS THE QUESTION......... if anyone is inclined:

I would like to find a way to copy values >0 from the individual summary lists, on different sheets
I would like to copy those values to a separate sheet
The goal is to create a list of materials to be ordered for that project

What function should I be using?


BACKGROUND:
I have been doing this manually
By automating this function, it eliminates the 'ID-10-T' factor (commonly known as human error)


anyone willing to help?

VBA code

Please Login or Register  to view this content.

The attached has the results. Click RUN button on "Composite Material Take Off".

You will get message box with tab name (Leave for now): just click OK when they appear.

I found on the first sheet that a part was listed twice so I removed one and just added quantity to first.

Code currently does not check for duplicate entries in whatever is the first sheet: could be changed to do so.

Give it a trial and let me know how you get on.

By database do you mean something like Access? I don't know enough about Access (or any other DB) to say whether or not it is the better option.

It is difficult to advise as I (we) don't have sufficient knowledge of your business.

Equally, perhaps you could just have one "materials" sheet with a column(s) to identify the category i.e, Receptacles, Switches etc.

In your sample file there are many common materials (was this just done as an easy way to get the sample data?).

THE VBA macro should work OK with any number of worksheets so give it a good "Volume" test with as many sheets as is practical.