Subtotal in header
 

Hi All,

I have a sheet subtotalled by column E with page breaks. I'd like to display
the value of column E on a particular page in the header of that page. How
can I do that?
E.g.
Page1 (requested value in header: AC06)
A B C D
E F
2006.05.22 5 98 24/2006 AC06 2 000
2006.05.22 5 98 26/2006 AC06 2 000
AC06 Subtotal 4 000
Page2 (requested value in header: BB08)
2006.05.18 5 96 0054187 BB08 44 800
2006.05.18 5 96 0054188 BB08 40 250
2006.05.19 5 97 0054195 BB08 42 840
BB08 Subtotal 127 890

Thanks,
Stefi

Subtotal in header
 

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If .Name = "Sheet1" Then
.PageSetup.LeftHeader = .Range("AC06").Value
End If
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


---
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Hi All,

I have a sheet subtotalled by column E with page breaks. I'd like to
display
the value of column E on a particular page in the header of that page. How
can I do that?
E.g.
Page1 (requested value in header: AC06)
A B C D
E F
2006.05.22 5 98 24/2006 AC06 2 000
2006.05.22 5 98 26/2006 AC06 2 000
AC06 Subtotal 4 000
Page2 (requested value in header: BB08)
2006.05.18 5 96 0054187 BB08 44 800
2006.05.18 5 96 0054188 BB08 40 250
2006.05.19 5 97 0054195 BB08 42 840
BB08 Subtotal 127 890

Thanks,
Stefi

Subtotal in header
 

Something like:

Sub Macro1()
ActiveSheet.PageSetup.CenterHeader = Range("C3").Value
End Sub

will put the value of any cell (here C3) in the header.

If you are producing a page-by-page report with a different header for each
page, then you would use a macvro that performs setup-and-print for each page.
--
Gary's Student


"Stefi" wrote:

Hi All,

I have a sheet subtotalled by column E with page breaks. I'd like to display
the value of column E on a particular page in the header of that page. How
can I do that?
E.g.
Page1 (requested value in header: AC06)
A B C D
E F
2006.05.22 5 98 24/2006 AC06 2 000
2006.05.22 5 98 26/2006 AC06 2 000
AC06 Subtotal 4 000
Page2 (requested value in header: BB08)
2006.05.18 5 96 0054187 BB08 44 800
2006.05.18 5 96 0054188 BB08 40 250
2006.05.19 5 97 0054195 BB08 42 840
BB08 Subtotal 127 890

Thanks,
Stefi

Subtotal in header
 

Thanks for your reply! My problem is that can I call a Sub at each page break
which makes this setup for me or should I write a macro that prints each
pages separately (running a custom page setup before all printouts)?

Regards,
Stefi


€žGary''s Student€� ezt Ã*rta:

Something like:

Sub Macro1()
ActiveSheet.PageSetup.CenterHeader = Range("C3").Value
End Sub

will put the value of any cell (here C3) in the header.

If you are producing a page-by-page report with a different header for each
page, then you would use a macvro that performs setup-and-print for each page.
--
Gary's Student


"Stefi" wrote:

Hi All,

I have a sheet subtotalled by column E with page breaks. I'd like to display
the value of column E on a particular page in the header of that page. How
can I do that?
E.g.
Page1 (requested value in header: AC06)
A B C D
E F
2006.05.22 5 98 24/2006 AC06 2 000
2006.05.22 5 98 26/2006 AC06 2 000
AC06 Subtotal 4 000
Page2 (requested value in header: BB08)
2006.05.18 5 96 0054187 BB08 44 800
2006.05.18 5 96 0054188 BB08 40 250
2006.05.19 5 97 0054195 BB08 42 840
BB08 Subtotal 127 890

Thanks,
Stefi

Subtotal in header
 

Hi Bob,

Sorry, perhaps my explanation was not clear: AC06 is not a cell reference,
it's simply a code that by chance resembles XL cell references. On the other
hand your macro places the same value in the header of all pages. I need a
different value on each pages according to the value of column E on that page.
Any other idea? See also my post to Gary"s Student!

Regards,
Stefi


€žBob Phillips€� ezt Ã*rta:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If .Name = "Sheet1" Then
.PageSetup.LeftHeader = .Range("AC06").Value
End If
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


---
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Hi All,

I have a sheet subtotalled by column E with page breaks. I'd like to
display
the value of column E on a particular page in the header of that page. How
can I do that?
E.g.
Page1 (requested value in header: AC06)
A B C D
E F
2006.05.22 5 98 24/2006 AC06 2 000
2006.05.22 5 98 26/2006 AC06 2 000
AC06 Subtotal 4 000
Page2 (requested value in header: BB08)
2006.05.18 5 96 0054187 BB08 44 800
2006.05.18 5 96 0054188 BB08 40 250
2006.05.19 5 97 0054195 BB08 42 840
BB08 Subtotal 127 890

Thanks,
Stefi

Subtotal in header
 

That may be possible, but I don't know how. The difficulty that I see is
that there is no way to add logic to the spooling.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Hi Bob,

Sorry, perhaps my explanation was not clear: AC06 is not a cell reference,
it's simply a code that by chance resembles XL cell references. On the
other
hand your macro places the same value in the header of all pages. I need a
different value on each pages according to the value of column E on that
page.
Any other idea? See also my post to Gary"s Student!

Regards,
Stefi


"Bob Phillips" ezt írta:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If .Name = "Sheet1" Then
.PageSetup.LeftHeader = .Range("AC06").Value
End If
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


---
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Hi All,

I have a sheet subtotalled by column E with page breaks. I'd like to
display
the value of column E on a particular page in the header of that page.
How
can I do that?
E.g.
Page1 (requested value in header: AC06)
A B C D
E F
2006.05.22 5 98 24/2006 AC06 2 000
2006.05.22 5 98 26/2006 AC06 2 000
AC06 Subtotal 4 000
Page2 (requested value in header: BB08)
2006.05.18 5 96 0054187 BB08 44 800
2006.05.18 5 96 0054188 BB08 40 250
2006.05.19 5 97 0054195 BB08 42 840
BB08 Subtotal 127 890

Thanks,
Stefi

Subtotal in header
 

I think you would have to calc the subtotal for page 1, put it in the
header, print, then calc page 2, put it in the header and print etc. I don't
think you can interrupt the print spool in the way you are thinking. This
could be a problem if you are printing to a file or if others could be
printing pages in between yours but otherwise it should be okay.

Jeff

"Stefi" wrote in message
...
Thanks for your reply! My problem is that can I call a Sub at each page
break
which makes this setup for me or should I write a macro that prints each
pages separately (running a custom page setup before all printouts)?

Regards,
Stefi


"Gary''s Student" ezt írta:

Something like:

Sub Macro1()
ActiveSheet.PageSetup.CenterHeader = Range("C3").Value
End Sub

will put the value of any cell (here C3) in the header.

If you are producing a page-by-page report with a different header for
each
page, then you would use a macvro that performs setup-and-print for each
page.
--
Gary's Student


"Stefi" wrote:

Hi All,

I have a sheet subtotalled by column E with page breaks. I'd like to
display
the value of column E on a particular page in the header of that page.
How
can I do that?
E.g.
Page1 (requested value in header: AC06)
A B C D
E F
2006.05.22 5 98 24/2006 AC06 2 000
2006.05.22 5 98 26/2006 AC06 2 000
AC06 Subtotal 4 000
Page2 (requested value in header: BB08)
2006.05.18 5 96 0054187 BB08 44 800
2006.05.18 5 96 0054188 BB08 40 250
2006.05.19 5 97 0054195 BB08 42 840
BB08 Subtotal 127 890

Thanks,
Stefi

Subtotal in header
 

Well, thanks to all of you, finally I wrote a macro printing separately the
sections with the same value in the column by which subtotals are created and
counting the number of pages belonging to these values (on the base of
HPagebreaks(n)).

Nevertheless it would be beneficious to include a new feature in a later
version of XL (something like an event AfterPageBreak)!

Regards,
Stefi


€žJeff Standen€� ezt Ã*rta:

I think you would have to calc the subtotal for page 1, put it in the
header, print, then calc page 2, put it in the header and print etc. I don't
think you can interrupt the print spool in the way you are thinking. This
could be a problem if you are printing to a file or if others could be
printing pages in between yours but otherwise it should be okay.

Jeff

"Stefi" wrote in message
...
Thanks for your reply! My problem is that can I call a Sub at each page
break
which makes this setup for me or should I write a macro that prints each
pages separately (running a custom page setup before all printouts)?

Regards,
Stefi


"Gary''s Student" ezt Ã*rta:

Something like:

Sub Macro1()
ActiveSheet.PageSetup.CenterHeader = Range("C3").Value
End Sub

will put the value of any cell (here C3) in the header.

If you are producing a page-by-page report with a different header for
each
page, then you would use a macvro that performs setup-and-print for each
page.
--
Gary's Student


"Stefi" wrote:

Hi All,

I have a sheet subtotalled by column E with page breaks. I'd like to
display
the value of column E on a particular page in the header of that page.
How
can I do that?
E.g.
Page1 (requested value in header: AC06)
A B C D
E F
2006.05.22 5 98 24/2006 AC06 2 000
2006.05.22 5 98 26/2006 AC06 2 000
AC06 Subtotal 4 000
Page2 (requested value in header: BB08)
2006.05.18 5 96 0054187 BB08 44 800
2006.05.18 5 96 0054188 BB08 40 250
2006.05.19 5 97 0054195 BB08 42 840
BB08 Subtotal 127 890

Thanks,
Stefi