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.

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.

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.

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.

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

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