Transferring data from multiple sheets in workbook to one sheet

[BillB]

I have a workbook with 10 sheets. One sheet is the "master"; initially it is
blank except for the headings at the top of 6 columns. The other 9 sheets
represent 9 categories of products customers can buy. The names for each of
these 9 sheets reflect the product categories. Each of these 9 sheets
includes 10 - 70 rows; each row represents a different product; each of these
sheets has six columns (mfg, part#, descrip, cost ea, qty, total cost).

Starting with all "qty" at zero; the customer would go through each of the 9
sheets and enter the quantity of each product he wants to buy. At this point
I want a copy of all information in a row in which the "total cost" is
greater than zero to be placed in the "master" sheet which would have the
same six columns as the other nine sheets. In this way I can look at only the
master sheet to see what the customer has ordered. Of course, if the customer
goes back into any of the 9 sheets and changes products or quantities, I
would want these changes reflected in the Master Sheet.

How would I do this in Excel 2003 (SP3)? Thank you.

 

[Joel]

You really need a macro to accomplish what you are requesting. I wouldn't
make it automatic but to have a control button that the user pushes to update
the summary worksheet after al the changes are made. the macro isn't hard to
write and I can easily write the macro if that is the way youo want it done.

 

[Max]

Joel,

It would be great if you could share the macro which accomplishes the OP's
desired functionalities. Thanks in advance.

Max

 

[Joel]

The macro is simple

Sub MakeMaster()

Set MasterSht = Sheets("Master")

NewRow = 2
For Each sht In Worksheets
If UCase(sht.Name) <> "MASTER" Then
RowCount = 2
Do While sht.Range("A" & RowCount) <> ""
If sht.Range("A" & RowCount) > 0 Then
sht.Rows(RowCount).Copy _
Destination:=MasterSht.Rows(NewRow)
NewRow = NewRow + 1
End If
Loop
End If
Next sht
End Sub

 

[Max]

Thanks, Joel. Hit some problems trying it out.

Prepared this sample set-up with 3 sheets: Cat1, Cat2, Master

In sheet: Cat1

Qty Part# ProdDesc
10 Dat11
2 10 Dat12
5 15 Dat13



In sheet: Cat2

Qty Part# ProdDesc
1 14 Dat21
15 Dat22
3 17 Dat23



Then in sheet: Master,
started with only the headers in A1:C1

Qty Part# ProdDesc


When the sub is run, the expected
results would be something like this:

Qty Part# ProdDesc

2 10 Dat12
5 15 Dat13

1 14 Dat21

3 17 Dat23



When I ran your sub, I got this in Master,
it hung so I CTRL+Breaked it

Qty Field1 Desc

1 14 Dat21
1 14 Dat21
1 14 Dat21

(Repeat interminably ...)

How can it be made to produce the expected results?

Max

 

[Max]

Sorry, the earlier paste got a bit warped

Here's how it is for Cat1 & Cat2

Cat1

Qty Part# ProdDesc
b ... 10 Dat11
2 ... 10 Dat12
5 ... 15 Dat13

Cat2

Qty Part# ProdDesc
1 ... 14 Dat21
b ... 15 Dat22
3 ... 17 Dat23

b = blank (no qty input)