Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transferring data from multiple sheets in workbook to one sheet
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transferring data from multiple sheets in workbook to one sheet
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. "BillB" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transferring data from multiple sheets in workbook to one sheet
Joel,
It would be great if you could share the macro which accomplishes the OP's desired functionalities. Thanks in advance. Max |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transferring data from multiple sheets in workbook to one shee
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" wrote: Joel, It would be great if you could share the macro which accomplishes the OP's desired functionalities. Thanks in advance. Max |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transferring data from multiple sheets in workbook to one shee
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transferring data from multiple sheets in workbook to one shee
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transferring data from multiple sheets in workbook to one sheet | Excel Discussion (Misc queries) | |||
Transferring data from multiple sheets in workbook to one sheet | Excel Worksheet Functions | |||
Transferring data from multiple sheets in workbook to one sheet | Excel Worksheet Functions | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
copy data in one sheet to multiple sheets in same workbook | Excel Worksheet Functions |