Manage Page Breaks w/ VBA

[DannyDont]

I am attempting to determine where page breaks occur as I am writing to a worksheet. This seems rather simple at the onset. However, I am copying rows from another worksheet and then setting the Row Height to that of the copied row. So the page break can come at any variable place in the worksheet. I use the following code to find a page break: NOTE: If there is a function that I should use to add the code in this message, tell me as I don't know just what I am doing!
=====
If ActiveCell.EntireRow.PageBreak <> -4142 Then '(NOTE: I found this on the Internet somewhere - I don't like codes like this but ???)
MsgBox "Break above this cell"
Range("A" & Control_Row_Value - 2).EntireRow.Insert
Range("A" & Control_Row_Value - 2).Value = "Header Inserted Here"
Control_Row_Value = Control_Row_Value + 1
Range("A" & Control_Row_Value).Value = "zzzzz" 'Just printing something to see which row I have.
Merge_Header_1
End If
=====
This "sort of works" but gives me a page break indication above the current_row that I am writing to.
At this point I insert a new row, and place the heading there, then move my current_row to current_ row +1

This is well and good until I insert a row who's Row Height does not make the page break. Below is the code that I am using to copy a row from a second Worksheet and adjust it's height. Seems like I should be ale to use this routine on the inserted Row for the Page Header. I will try that. I think that this does not make any sense. Seems Like I need to set the inserted row Height to a row with the largest Row Height. ???? Can't explain too well here.)

This code below is used on every row that I copy from FORMAT to the current worksheet.
Control_Header_Row is the row value of the row copied from FORMAT. There are about 20 different rows in the FORMAT worksheet. The user of this application can change the row of any or all rows on the FORMAT worksheet so that the resultant output fits their needs.
=====
Sheets("FORMAT").Range("A" & Control_Header_Type & ":I" & Control_Header_Type).Copy _
Destination:=ActiveSheet.Range("A" & Control_Row_Value)
Control_Row_Highest = ActiveSheet.UsedRange.Rows.Count 'Need to have highest row as we added something
Range("A" & Control_Row_Highest).Select
Selection.RowHeight = Sheets("FORMAT").Range("A" & Control_Header_Type).RowHeight
=====
The first line does the copy from Sheet "FORMAT" to the Current_Row_Value in the active worksheet. The row copied is Control_Header_Type
The next line just tracks the Highest Row that has had data added to it.
The next line selects that row (should be the just copied row from FORMAT) to make it the active row.
The next line sets the Row Height of the row just copied to the Row Height of the row in the FORMAT worksheet.
NOTE: I track the Highest Row so that I can set the Print Area when I have finished processing. There is probably a much easier way to do that?
=====

There continues to be a bit of guess work involved so I am wondering someone can improve on this or has has a better answer?
Thank you in advance for your help.

Sorry for how confusing this must seem. Perhaps someone can understand????

Dan Foltz
Thanks in advance for any help you might provide.

 

[GWteB]

You can set the first row (or rows) to be the header on each page with:

ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"

I understand however, that each page (on paper) has to get his own header.
Where page breaks initial occur varies and depends, among other things, on the page setup of the worksheet, for instance: zoom, true size or fits on one page (eg. no page breaks at all). Enforcing a (horizontal) page break anywhere on your sheet (manual or with VBA) affects directly the following initial (= where Excel puts them...) page breaks. You may consider to enforce the page breaks on the desired places when all your copy/paste work is done; just before activating the print job. This can be done using the "Workbook_BeforePrint" event.

Consider to use a column to put in some "markers" on the rows where a new page should start. When you make this happen on the worksheet where you keep your pageheaders (the source), your "markers" also show up on the target sheet. On the target sheet you can exclude this column from the printarea or just make this column hidden.

Code to put in the module of ThisWorkbook could be like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)


    Const cHPG_marker   As String = "HPG-here"              ' your marker (could be any other string)


    Dim oWs             As Worksheet
    Dim oCell           As Range
    Dim oColumn         As Range
    Dim iColWidth       As Double
    
    Set oWs = ThisWorkbook.Worksheets("MySheetToPrint")     ' your sheet to print
    Set oColumn = oWs.Columns("A")                          ' the column to put your markers in
    
    If ActiveSheet Is oWs Then
    
        ' backup the actual width of the marker-column
        iColWidth = oColumn.ColumnWidth
        
        ' hide this column
        oColumn.ColumnWidth = 0
        
        
        ' clear all the pagebreaks
        oWs.ResetAllPageBreaks
        
        
        ' define proper range to search for your markers
        Set oColumn = Application.Intersect(oWs.UsedRange, oColumn)
        
        ' enforce Horizontal PageBreaks before the cells with your markers
        If Not oColumn Is Nothing Then
            For Each oCell In oColumn
                If oCell.Formula = cHPG_marker Then
                    oWs.HPageBreaks.Add Before:=oCell
                End If
            Next
        End If
        
        ' restore the column width
        oColumn.ColumnWidth = iColWidth
    
    End If
    
sub_finished:


    Set oColumn = Nothing
    Set oCell = Nothing
    Set oWs = Nothing
    
End Sub

Kind regards

 

[DannyDont]

Thank you for all of the good information.

However, you state:

"Consider to use a column to put in some "markers" on the rows where a new page should start. When you make this happen on the worksheet where you keep your pageheaders (the source), your "markers" also show up on the target sheet. On the target sheet you can exclude this column from the printarea or just make this column hidden."

The issue is that I do not know where the page should be until Excel tells me that I just printed a row that caused a page break. If this is the case, how would I know where to put the markers? There is no trigger from the data or anything external to Excel. All the page header does is print the same header with an updated page number and there is no specific place where it goes ... that is it goes in when there is a page break created by Excel as I modify row heights as I go. In one sense the page header is not really needed. But I like to have page numbers on the documents that will be generated from this effort.

Could I create the new worksheet without any headers (fit to page) and then change to 100% and go back through the new worksheet, find the breaks, and then add the headers?

Or is there a way to use: "
ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"

and change a value in the Title Rows - that is change the Page Number?

I do appreciate you helping out. AND how do you add the code into the code box like you did?

DannyDont

 

[GWteB]

I'm not sure what you're trying to achieve. If you only want to show page numbers on each page, you better stick to the pageheader/pagefooter, rather then putting a formula in (one of) the title row(s). To determine where Excel puts the automatic breaks, you can use code (as follows) but please take into account (and perhaps unnecessary to mention), that when you overide any automatic pagebreak, the next automatic pagebreak in line obviously pops up on a higher row number than before the overide. Code to get the exact place of the n-th horizontal pagebreak could look like this:

Sub Example()


    Dim lRowNumber      As Long
    Dim n               As long


    n = 2
    
    lRowNumber = GetRowOfPageBreak(ActiveSheet, n)


    ' If lRowNumber = 0 then we were searching for a non-existing horizontal pagebreak


End Sub

The above example can be used to obtain the second horizontal pagebreak (if it even exists...) on the active worksheet. It's calling a separate function:

Public Function GetRowOfPageBreak(argWs As Worksheet, argPageBreak As Long) As Long


    Dim oBreaks         As HPageBreaks


    ' make a proper reference
    Set oBreaks = argWs.HPageBreaks


    ' return the row number where a new page starts
    If oBreaks.Count >= argPageBreak Then
        GetRowOfPageBreak = oBreaks(argPageBreak).Location.Row


    Else
        ' at this point the given break is not found; return with a non-existing row
        GetRowOfPageBreak = 0


    End If
    
    'cleanup
    Set oBreaks = Nothing


End Function

Finally, sometimes it's more convenient managing the pagebreaks manually in the page preview.


Kind regards.

 

[DannyDont]

You brought up a thought that had evaded me. Using the Header/Footer is a GREAT idea. I had someone that needed the program like right now, so I simply removed the Header process and just let Excel take charge. I do however believe that an Excel Header section will indeed solve the issue. I wish that I could better describe what I am attempting to do so you could understand. I am going to try, not that you need to read or understand.

I am writing a General Use application that many folks will use, and they will use it in different ways. The basic process is to read a CSV File that contains records with specific Record Types or Codes and print out a report. Within each record type, there is varying information. I process each record type as a separate routine within VBA but use common routines to read, write, and check for page breaks. There are three worksheets in the workbook. One is Welcome which contains a MENU with several options. Two is a Preferences sheet where users can set different options so that their output is tailored to their desires. Three is a FORMAT sheet. On this sheet, each Record Type found in the CSV File has it's own row. This row defines all of the things that this Record Type has to offer. I allow the user to change anything that Excel allows. This includes changing the Row Height of various (any) Record Type row found in the MASTER worksheet. This allows any user to have the output look as he wishes. So I think you can see that I never really know when a page break will occur because it is depending on a large number of variables, including but not limited to; the contents of the CSV file, Preference setting for the individual user, and the formatting set in the FORMAT worksheet for a user. With all of this data and information, I create another worksheet that contains the output of the routines that function for each of the Record Types, and other information. I have found that if I do check for a page break as I write a new row, I can insert a Header type row at the current_row - 1 (or something like that), increase my current_row by one, and then write the current output for the CSV Record processing routine. I still have some issues with it and it does create at least two row updates for each one created. So I am going to attempt to work with the Header idea. Of course one of the Preferences that I provide is to print Headers or not to print Headers. Hummmmmm!

If I knew how to attach a file I would attach the CSV File and the resultant output Workbook. But I am new to this posting stuff.

In any case, I REALLY appreciate your help. The Header might just be the ticket. Thanks

I just went back and re-read your ideas. I like the Function that finds the page-break row. Could I do this when the new workbook/worksheet was created? If I could do that, I could insert my own Header with any changing information that I would like. If I do the insert of a row, will the next header row that I find be adjusted accordingly? That is if I insert a row, the page breaks below this area will potential change and if I have many pages I will insert a row at each page break, thereby continuing to increase the number of rows in the worksheet. Did I explain that well enough?

How would I modify your routine to find pagebreak-1, pagebreak-2, ..., pagebreak-n ... ?

 

[GWteB]

I'm still not sure about your goal. You're letting the users of your workbook combine different types of records on one and the same NEW worksheet, while each record type needs its own title row, am I right? If that's the case, maybe you should consider another approach by using more worksheets: each record type on a different EXISTING worksheet. This has the advantage that each record type can have its own appropriate title, pre defined by you, which will apear on every page on paper, regardless the position of the page break.

I like the Function that finds the page-break row. Could I do this when the new workbook/worksheet was created?

Yes you can, by using the NewSheet event in the ThisWorkbook module of your project ... As far as I know there is no event that's triggered by Excel, when a new workbook is created. Since you're creating a new workbook using your own sub/function, you can add your desired code in the same sub/function.

Private Sub Workbook_NewSheet(ByVal [COLOR="#0000FF"]Sh[/COLOR] As Object)

    MsgBox [COLOR="#0000FF"]Sh[/COLOR].Name    ' show me the name of the just added worksheet

End Sub

How would I modify your routine to find pagebreak-1, pagebreak-2, ..., pagebreak-n ... ?

Simply change the value of n ...

Sub Example()


    Dim lRowNumber      As Long
    Dim [COLOR="#0000FF"][B]n[/B][/COLOR]               As Long


    [COLOR="#0000FF"][B]n[/B][/COLOR] = 2
    
    lRowNumber = GetRowOfPageBreak(ActiveSheet, [COLOR="#0000FF"][B]n[/B][/COLOR])


    [COLOR="#008000"]' If lRowNumber = 0 then we were searching for a non-existing horizontal pagebreak[/COLOR]


End Sub

To obtain the start of the last page of the active worksheet for example, using the GetRowOfPageBreak function from my previous post ...

Sub Example_2()

    Const ciMaxHPageBreaks  As Long = 1026       [COLOR="#008000"]' as defined by Microsoft[/COLOR]

    Dim lRowNumber          As Long
    Dim i                   As Long

    For i = ciMaxHPageBreaks To 1 Step -1

        lRowNumber = GetRowOfPageBreak(ActiveSheet, i)
        If lRowNumber <> 0 Then Exit For

    Next i

    [COLOR="#008000"]' at this point lRowNumber contains de first row of the last page[/COLOR]

End Sub

I hope this is of some help. Succes with your project.

Kind regards.