Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sum Rows to Totals Sheet
Hi,
I have a workbook with 30 - 40 sheets. Each of these sheets are use for seperate customers and contain rows of orders. After each orde type there is a Totals row. I would like to be able to take a copy all of the different Totals row (and the customer name) to a master sheet. e.g. Sheet: Customer One Product Customer Price Weight 1 Customer One 3 12 1 Customer One 3 8 1 Customer One 3 10 1 Customer One 3 15 12 45 2 Customer One 9 2 2 Customer One 9 5 2 Customer One 9 7 2 Customer One 9 11 2 Customer One 9 5 45 30 Therefore, copy the two Totals rows into 'Totals' worksheet appearin as below 1 Customer One 12 45 2 Customer One 45 30 thanks in advance Hamis -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sum Rows to Totals Sheet
Hamish,
the first question is is this a run on demand report or something that needs to be continuously updated? A lot depends on the methods used to input the data. Is the data input consecutively on the customer sheet, or is that data imported from a job control/costing package? If input on the sheet, then is it entered consecutively order by order, or is it entered consecutively order by product. If the latter, then you can simply set up subtotal rows beneath each product and reference those fields on your master sheet. I suspect however, that your orders are entered consecutively by order, adn therefore, to produce your subtotals, you sort by product. Are you using the Data/subtotal menu item to produce your subtotals? Let's assume that you do so, and that this will be a run on demand report. Your process would be something like: -Clear the report area on the master sheet. -Goto customer 1 sheet -set a variable to customer name (vCName) -sort the orders by product -subtotal by product, with sum totals in qty and $ value. -run through the product column until the right 5 characters of the cell ="Total" -store the values in the subtotals in two more variables (vQty and vValue -goto the first line in the report area of master sheet -write the three variables in the appropriate columns -return to the customer sheet -go down one row, if left 5 characters ="Grand" for grand total, goto next customer sheet, if not then loop some of the methods and statements you should have a look at prior to writing the code would be Activecell.selection activecell.offset for each sheet in sheets and the difference between activesheet and sheets (index_value) Steve -----Original Message----- Hi, I have a workbook with 30 - 40 sheets. Each of these sheets are used for seperate customers and contain rows of orders. After each order type there is a Totals row. I would like to be able to take a copy all of the different Totals rows (and the customer name) to a master sheet. e.g. Sheet: Customer One Product Customer Price Weight 1 Customer One 3 12 1 Customer One 3 8 1 Customer One 3 10 1 Customer One 3 15 12 45 2 Customer One 9 2 2 Customer One 9 5 2 Customer One 9 7 2 Customer One 9 11 2 Customer One 9 5 45 30 Therefore, copy the two Totals rows into 'Totals' worksheet appearing as below 1 Customer One 12 45 2 Customer One 45 30 thanks in advance Hamish --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sum Rows to Totals Sheet
Hi Steve - thanks for your advice.
This report gets run on demand, 2 or 3 times per week. Lines are consecutive orders, by product. Each line order is create from a stock list with manual input (all within excel). The subtotal rows are currently being set up manually already - howeve there can be many different lines, for many different products, on eac sheet - all of which will need a 'Total' line. The location (rows) wil not be consistent from period to period. I am currently not using the subtotals option - but will look int that, it sounds useful. With regard to your suggestions on the process, I actually don't hav much experience writing VBA code, so was looking for something that wa prewritten. Hope you can help. Hamis -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sum Rows to Totals Sheet
Just to clarify a bit.....
I need VBA code to: - copy every row from multiple worksheets, that contain the wor 'Total', to a TOTALS sheet. - for every row that is copied, also include cell contents (from Col & Col E in the above row) at the end of the row in two new column -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
Extracting Rows to Make A Totals Sheet | Excel Worksheet Functions | |||
Extracting rows from worksheets to make a totals sheet | Excel Discussion (Misc queries) | |||
Copy and Paste Macro for front totals sheet | Excel Discussion (Misc queries) |