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

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

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

Doug, here's another way around it; it's kind of complicated, but it should
work. I'm going to use an example of only one sheet. Here are the logical
steps
-Find the PrintArea of the sheet
-Find all pagebreaks (manual & automatic) - it will make your life much
easier if you can make the simplifying assumption that all breaks are
horizontal (pages only go down the worksheet, not down and accross)
-From the pagebreaks, you will know the total page count (for a "Page x of
Y" footer)
-Determine the addresses for all the individual printed sheets - we'll call
these Print Area Pages
-For each Print Area Page, calculate the sum, modify the PageSetup, print

If you decide to go this route, I can share the code that identifies the
Print Area Pages.

Bill



"Doug F." wrote:

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