Printing subtotals on each page

[slopsjon]

I want to print out a spread sheet with subtotals on each page but the number
of rows and height of rows maybe different each time and I don't want to have
to manually adjust the print area and move the subtotal row around. Is there
anyway to do this automatically in Excel or do I need something with a
reporting tool.

 

[Per Erik Midtrød]

I want to print out a spread sheet with subtotals on each page but the number
of rows and height of rows maybe different each time and I don't want to have
to manually adjust the print area and move the subtotal row around. Is there
anyway to do this automatically in Excel or do I need something with a
reporting tool.

If you go to subtotals on the menu instead of writing your own
subtotal function just check "Page break between groups"

Per Erik

 

[slopsjon]

Thanks for the reply. I tried the Subtotals from the menu but that just seems
to Page Break Groups but doesn't put a subtotal on a page that over runs more
than one page. I only have one list of data with no grouping and need a
subtotal of columns on each page.
e.g.
Page 1
row
row
row
end of page running total
Page 2
row
row
row
end of page running total
Page 3
row
row
row
last page Total

Is that possible? Ta.

 

[Pete_UK]

It is not possible using the Data | Subtotals routine - you would need
your own VBA routine to do that.

Pete

 

[slopsjon]

Thanks for the reply.

Does that mean I would have to work out how many rows and height of cells
will fit on a page with a row of subtotals. In other words would I have to
write my own reporting tool.

 

[Pete_UK]

Yes, that is correct if you wanted a general purpose routine, and you
would also need to take account of any header/footer space etc.

However, you may decide that you want 50 rows per page, say, so that
would make it easier to code.

Hope this helps.

Pete

 

[slopsjon]

Thanks for the reply.

One of the columns has wrapped text so the row height varies from row to
row. So I think this would be better of done in Access so I can use the an
Access Report. Unless someone has already written a general VBA printing
routine for Excel.

Thanks for you help Pers and Pete.

 

[Pete_UK]

You're welcome - good luck with the Access route.

Pete

 

[Jim Cone]

Re: "Unless someone has already written a general VBA printing routine for Excel"

Since you asked... you might want to try out the commercial Excel add-in
"Special Print" (with a free 3 week trial) from yours truly.

It has a "Rows to Repeat at Bottom" feature that creates a copy of your
worksheet with specified row(s) added to the bottom of each printed page.
You would have to enter the subtotal formulas after the new page
was created as only values are transferred to the new sheet.
Download from the Products page at...
http://www.realezsites.com/bus/primitivesoftware
--
Jim Cone
San Francisco, USA
(Excel Add-ins / Excel Programming)





"slopsjon"
wrote in message
Thanks for the reply.
One of the columns has wrapped text so the row height varies from row to
row. So I think this would be better of done in Access so I can use the an
Access Report. Unless someone has already written a general VBA printing
routine for Excel.
Thanks for you help Pers and Pete.

 

[slopsjon]

Thanks for the reply.

I think the user justs wants to enter the rows of data and hit print rather
than have to format the print area and select the subtotals to print on each
page.

I found a solution in Access, a bit of code needed as access reports doesn't
do it. You have to use the page footer. Here is an article about it.

Summing in reports
http://office.microsoft.com/en-us/access/HA011224441033.aspx?pid=CL100570041033