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

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/

.

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

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