I have to print a report, containing a few hundred rows of numerical data. I
want Excel to automatically print subtotals of each column at the bottom of
each printed page. I can do it manually, by counting the number of rows that
would fit on a page, and adding the subtotals at the correct position. But,
as soon as you add or delete rows in your ranges, you have to change all your
subtotals again.

Excel doesn't have this kind of feature.

Maybe you can add your own page breaks and be a little more conservative on the
number of rows per page. If 60 fit, then use 50 per page. Then you could
insert 10 in each page before causing too much trouble.

Another option is to add a column and put a page indicator down that column.
I'd use column A and either hide it or leave it out of the print range.

Then I could use Data|subtotal on that column to get totals and page breaks
where I wanted.

If I had to add more rows to any page, I could remove the subtotals and then put
new values in column A and reapply data|subtotals.

You may want to experiment with something like:
=int(row()/60)
drag down the column
convert to values
data|subtotal

to see if it gives you what you want (60 was my guess. it may not work with
your print setup.)

Stiv wrote:

I have to print a report, containing a few hundred rows of numerical data. I
want Excel to automatically print subtotals of each column at the bottom of
each printed page. I can do it manually, by counting the number of rows that
would fit on a page, and adding the subtotals at the correct position. But,
as soon as you add or delete rows in your ranges, you have to change all your
subtotals again.

--

Dave Peterson

My Excel add-in "Special Print" will do that.
Give the trial version a tryout (its free) by just asking for it.
I will forward it as a zipped email attachment.
Remove xxx from my email address... james.coneXXX at comcast.netXXX
Please provide your real name and geographic location.
--
Jim Cone
Portland, Oregon USA



"Stiv"
wrote in message
I have to print a report, containing a few hundred rows of numerical data. I
want Excel to automatically print subtotals of each column at the bottom of
each printed page. I can do it manually, by counting the number of rows that
would fit on a page, and adding the subtotals at the correct position. But,
as soon as you add or delete rows in your ranges, you have to change all your
subtotals again.

If you want a macro I can esily write one. How many Rows per page do you
want before addig the subtotals?

"Stiv" wrote:

I have to print a report, containing a few hundred rows of numerical data. I
want Excel to automatically print subtotals of each column at the bottom of
each printed page. I can do it manually, by counting the number of rows that
would fit on a page, and adding the subtotals at the correct position. But,
as soon as you add or delete rows in your ranges, you have to change all your
subtotals again.