Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Incremented Footers (2000)

    I hope someone can help. I have a coworker that has a excel spreadsheet with credit card deposit information. She needs to have the data all on one sheet of the spreadsheet for one reason or another, but when she prints she wants to insert page breaks between each month. This I have done for her, but now she wants a footer on each page giving the month and year that is covered on that page.

    Is there any way to increment a footer?

    Thanks in advance for the help <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incremented Footers (2000)

    I was thinking of using VBA for this. Here is some code that uses pagebreak location to determine the printarea and prints all pages with a different footnote (actually the contents of the cell just above the pagebreak). Don't know if this is what you want. I've put it in a sub, but you can put similar code in the BeforePrint event of the workbook.

    <pre>Sub PrintAreaWithPageBreaks()
    Dim Pages As Integer
    Dim PageBegin As String
    Dim PrArea As String
    Dim i As Integer
    Dim q As Integer
    Pages = ActiveSheet.HPageBreaks.Count
    PageBegin = "$A$1"
    For i = 1 To Pages
    If i > 1 Then PageBegin = ActiveSheet.HPageBreaks(i - 1).Location.Address
    q = ActiveSheet.HPageBreaks(i).Location.Row - 1
    PrArea = PageBegin & ":" & "$H$" & Trim$(Str$(q))
    ActiveSheet.PageSetup.PrintArea = PrArea
    ActiveSheet.PageSetup.CenterFooter = Cells(q, 1)
    ActiveSheet.PrintOut copies:=1
    Next i
    End Sub
    </pre>


  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Incremented Footers (2000)

    Hi Hans,

    Well, thanks again. Of course, this leads to more questions.

    One thing that always bothered me and now is a good time to ask: when one looks at a sheet in print preview that results in >1 page and returns to normal view, one sees dotted lines where the breaks are. How does one get rid of the dotted lines?

    I recall the post you referred to. But there was no attachment in the originator's post so it was hard for me to see what he wanted or how your solution fit his problem.

    For my situation, I don't really care about breaking the page in any artificial way, as the other person wanted to do by months. I just want to account for all the printed pages from a sheet via the footer page numbering. So here's a bunch more questions:
    1. I noticed there is also an ActiveSheet.VPageBreaks.Count property. So I'd need to account for this. So just experimenting a little, I put some cell entries to cause a horiz and a vert page break. However, the 4th page in the lower right of the 2x2 grid of pages (ie 2x2 print areas) had nothing in it. How would I NOT print that page? (ie check for blank pages)

    2. Since the H/V-PageBreaks property says literally how many breaks there are, it is possible to have no breaks if, for example, you have only an entry in A1. Does your code (For i=1 to Pages) skip the loop entirely if there are no HPageBreaks? (assume the other person would have >1page). If there are no breaks, I guess the page setup control (eg, the statement for ...CenterFooter=...) would have to receive a value some other way.

    3. In my trivial little experiment (with pages with upper left cells at A1, N1, A36, N36), it seems counterintuitive that the ....Hpagebreak(1).Location.Address gave $A$36 while the ...VPageBreak(1)... gave $N$1. This did NOT seem to depend on whether my output was down then over or over then down (I changed the print direction setting to see if this made a difference but did not close the workbook after doing so to see if this would change anything - assume not). What is the correct interpretation of the value reported by ...Location.Address?

    4. I'd just like to make sure I understand the rest of the code, especially since there was no workbook attached to see how this was working:
    a. set PageBegin to $A$1 - no problem
    b. loop to number of pagebreaks (assume at least 1 or else my issue #2 above); within loop:
    ---1. If i>1 gets location of "current" break - no problem
    ---2. q=...Row -1 gets number of row above the break (even tho this is supposedly Horizontal) - no problem
    ---3. PrArea=... seems to be defining a range for the area.
    -------why do you concatenate ":" and "$H$" as 2 separate strings as opposed to ":$H$" - just style?
    -------why couldn't q have been concatenated directly to $H$, instead of using Trim$(Str$(q))? I understand the Str$ returns q with a space for the sign and Trim$ gets rid of the space. But doesn't the concatenation of an integer also accomplish this? Is this another good programming style item?
    -------should I assume the other person's sheet went from col A to col H only and nothing to right of H?
    ---4. I think I understand the next 2 statements: assign the concatenated print area to the property, create the footer with the information in col A of the row just above the break.
    ---5. ActiveSheet.PrintOut: does this actually cause the printing?

    5. And given all of this, it seems like I would NOT use the Excel4 call that you posted in the previous reply. Is this correct?

    Thanks again.

    Fred

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incremented Footers (2000)

    <hr>One thing that always bothered me and now is a good time to ask: when one looks at a sheet in print preview that results in >1 page and returns to normal view, one sees dotted lines where the breaks are. How does one get rid of the dotted lines?
    <hr>

    Have you tried Tools|Options|View and uncheck Page breaks check box?

    Regards
    Ken

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incremented Footers (2000)

    Fred,

    I'm afraid I cannot answer all your questions. My post on page breaks is the only time I experimented with this feature. It is clear the code is based on several assumptions: 1) that there actually are pagebreaks present, otherwise you can check on the activesheet.hpagebreaks.count property <> 0; 2) that in this case there are no vertical breaks; 3) that the width of the page goes from columns A to H

    Of course, your case will be different. You asked: 'any ideas?'. Well, this is just an idea.
    I used a string variable to redefine the PrintArea for each pagebreak, as I wanted to print the area between two pagebreaks separately. Of course, this PrintArea changes from pagebreak to pagebreak as the user can insert pagebreaks wherever he wants.
    What you have to extract from the Hpagebreak.Location.Address is the row of the pagebreak, whereas from Vpagebreak.Location.Address you obtain the column. Combine both to know where you are in your spreadsheet and to define the PrintArea. If the PrintArea is blank, then a blank sheet will be printen unless you circumvent this: e.g. you can loop through each cell of the printarea and check if it is not empty; if all cells are empty, you lower the pagenumber by one and goes to the next pagebreak.
    The old Excel4 macro is of no need if you do it like this. If all printareas contain information, then this old Excel4 macro will return the total number of pages in the worksheet in one commandline; otherwise you have to write code to count the pages via pagebreaks.

    Activesheet.Printout indeed does the printing. I suggest to record a macro while you do a print action, or insert pagebreaks or so, you will learn about the code which is generated that way.

    Hope this helps.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Incremented Footers (2000)

    Ken,

    You got it.

    I was being fooled because I was looking for something more dynamic, thinking the option would also apply to when you open the workbook if it was on. I checked the Options | View after Print Preview and the dotted lines appeared when I returned to "normal" view. Sure enough, the option was being turned on (after print preview) and, somehow (?) turned off, even if saved with them on, when re-opening the workbook.

    Thanks much. This always bothered me. I was always looking for soemthing dealing with the Page Setup.

    Fred

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Incremented Footers (2000)

    Thanks Hans.

    I think you answered the most important questions. This got me what I really needed. I think it would be somewhat straightforward, with what you gave me, to write a general print routine in VBA. I think I could even create a header/footer that said what rows/columns appeared on that page.

    It seems like it's all based on the print area and changing it based on the pagebreaks (and living with the HPageBreak referring to a row - a horizontal divider - splitting the page in the vertical direction <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ). Checking to see if a printarea is all blank shouldn't be too hard.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •