Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal To Include Item Description On Subtotal Line | Excel Discussion (Misc queries) | |||
sort macro, subtotal and add lines after subtotal | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |