[Tom Ogilvy]

Are the sheets in different workbooks or all 15 sheets in the same workbook.

Assume in different workbooks, on the first sheet in the tab order, data
starts in cell A1 with column headers (so first requirement is on row 2).

Sub GrabSheets()

Dim varr As Variant
Dim rng As Range
Dim i As Long
'expand to include your workbooks.
varr = Array("Dept3.xls", "Dept42.xls", "Dept33.xls", _
"Dept36.xls", "Dept99.xls")

For i = LBound(varr) To UBound(varr)
Set rng = Workbooks(varr(i)).Worksheets(1). _
Range("A1").CurrentRegion
Set rng = rng.Offset(rng.Rows.Count, 0). _
Resize(rng.Rows.Count - 1)
rng.Copy ThisWorkbook.Worksheets(1). _
Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub

You can then sort the data on product code and then do data=Subtotal on
product code with sum for quantity to get product sums. Click on the second
button in the outline section that will appear in the left side of the sheet
and it will collapse to show only the sums by product code.

If you want to automate that part, you can turn on the macro recorder while
you do it manually.
Put the code in a general module in the workbook where the master list will
be consolidated on the first sheet.

Regards,
Tom Ogilvy



Ian Truslove wrote in message
...
Hi
I'm a Supplies Manager in a Hospital. Each day the clinical departments
supply me with a excel spreadsheet detailing their requirments for
consumables for the next day. I've created these spreadsheets using a
"lookup" table which fills in description,specification number and loction
within the stores if they type the code number of the product required.
I've
never used "lookup" before and have been feeling really smug as to my
cleverness !!!!!
BUT I NOW NEED SOME HELP FOR THE NEXT STAGE.

Problem
Each of the sheets ( approximately 15 ) may contain the same products,
this
means I might potentially go to the same location point within the stores
multiply times to collect the same product.
Question
Is it possible to load multiple worksheets ( all in exactly the same
format ) into a single worksheet to enable me to sort into stores location
and total required for each product?

With my limited knowledge I would cut and paste each of the sheets into
one
master sheet and then sort by location code, I would then total each of
the
quantitys required by product code. Unfortunately I suspect my super time
saving idea would take longer than the old way.

I would appreciate any thoughts or suggestions

Thanks
Ian Truslove