Thanks Dave, this will provide me a solution.

--
Doug F.


"Dave Peterson" wrote:

There's nothing in excel that will tell you the name of the worksheet being
printed.

You can group multiple sheets and print those selected sheets.

You can print the entire workbook.

But you can just change the footer for each sheet and it won't matter if you're
printing it or not.

Public Sub Worksheet_BeforePrint(Cancel As Boolean)
dim wks as worksheet
for each wks in me.worksheets
wks.pageSetup.RightFooter = "Page Amount = " _
& application.sum(wks.range("test"))
next wks
End Sub

This will blow up if there's any worksheet that doesn't have that range name
(Test). Could that happen?

Public Sub Worksheet_BeforePrint(Cancel As Boolean)
dim wks as worksheet
dim TestRng as range

for each wks in me.worksheets

set testrng = nothing
on error resume next
set testrng = wks.range("Test")
on error goto 0

if testrng is nothing then
'do nothing, skip that footer
else
wks.pageSetup.RightFooter = "Page Amount = " _
& application.sum(wks.range("test"))
end if

next wks
End Sub

(Untested, uncompiled)

Doug F. wrote:

Hi Dave,
Thanks for your notes. My last but perhaps biggest challenge is to get the
'dblSum' for the *page* being printed, as the single sheet when printed has
many pages. My fallback solution is a MS Access report linking to the xls.

--
Doug F.

"Dave Peterson" wrote:

There is no worksheet_beforeprint event.

And just make sure you only change the footer for the worksheet you want:

worksheets("somesheethere").PageSetup.RightFooter = "Page Amount = " & dblSum

I'd be careful and specify the sheet to get the dblSum from, too.

Doug F. wrote:

A followup.
If I have this code in the Worksheet_BeforePrint it doesn't show anything in
the footer, when it's in the Workbook_BeforePrint it does show in the footer
a sum but of the entire worksheet not just the page being printed. So, the
questions are, which event should I be using and can I restrict the range to
the printed page?

Thanks,
--
Doug F.

"Doug F." wrote:

Thanks Bill.
--
Doug F.


"Bill Pfister" wrote:

Here is a psuedocode approach. Your only issue will be determining the
proper range, but this is not a terribly difficult task.

set rngTest = ' here you need to specify the range that you want to sum that
is
' visible on this particular printed page
dblSum = Application.WorksheetFunction.Sum( rngTest )
ActiveSheet.PageSetup.RightFooter = "Page Amount = " & dblSum


Regards,
Bill



"Doug F." wrote:

On each page printed I want to sum a column on that page and print that total
on the bottom of that page, probably in the footer (ie a total amount for a
data entry balancing process performed elsewhere). Each page has its own
total it's not a running total. I've tried variations of the following where
'test' is a named column label:

Public Sub Worksheet_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = "Page Amount = " & Sum([test])
End Sub

It doesn't like the Sum function in this event.

Ideas? Thanks,
--
Doug F.

--

Dave Peterson


--

Dave Peterson