Print specific rows on new page at end of document

[sparky2205]

I have a spreadsheet that can vary greatly in length. It is used to record data from various processes which could result in the final document being 1 page or 10 pages.
There is a signoff section at the end of the document which compromises 7 rows. If the entirety of this section doesn't fit on one page at the end of the document I want the entire section to be moved to the next page so that the section is kept together. If there is enough room on the final page of the document it should remain on that page. The section must not be printed on every page, just the final page.

I don't like coming to the table with no starting attempt but my thought processes have led me down blind alleys to this point.
I was going in the direction of getting the last used row by hiding a piece of text in a hidden cell at the end of Column A, then using this as a starting point to subtract from the top of the page. Alas, I don't know how to get the top of the page so, as I said, blind alley.

Appreciate any thoughts.

 

[John_w]

You could look for some known text which occurs in the last signoff row, and move the last page break if it occurs in the 7 signoff rows. For example, this macro looks for "SIGNOFF" in column A.

Public Sub Move_Last_Page_Break3()

    Dim originalView
    Dim signoffEndCell As Range
    Dim lastHPageBreak As HPageBreak
        
    'Save current view and change it to Page Break Preview
    
    With ActiveWindow
        originalView = .View
        .View = xlPageBreakPreview
    End With

    'Look on the active sheet in the active workbook
    
    With ActiveWorkbook.ActiveSheet
        
        'Find last signoff row
        
        Set signoffEndCell = .Columns("A").Find(What:="SIGNOFF", After:=.Range("A1"), LookIn:=xlFormulas2, LookAt:=xlPart, _
                                                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        'If the last horizontal page break occurs within the 7 signoff rows move it above first signoff row
        
        If .HPageBreaks(.HPageBreaks.Count).Location.Row > signoffEndCell.Row - 6 Then
            .HPageBreaks(.HPageBreaks.Count).Delete
            .HPageBreaks.Add Before:=signoffEndCell.Offset(-6)
        End If
                
    End With
    
    'Restore original view
    
    ActiveWindow.View = originalView
    
End Sub

 

[sparky2205]

Hi John,
thanks for your response and apologies for my tardy reply. The dreaded Covid caught up with me last week so I've only just gotten back to work and access to my files.
I have a bit of catching up to do so it will be later in the week before I test this out on my file. But I will be back before the end of the week as I am keen to try this out.

 

[sparky2205]

Hi John,
this is very close. It is pushing the signing section onto the next page, but it appears to always do this regardless of how many rows are on the sheet.
What I want:
If there is room at the end of the page for the signing section then leave it at the end of the page. i.e. if the report is one page but there is room on that one page for the complete signing section leave it on that page. If there is only room for part of the signing section then move the entire signing section to the next page. The same if the report is 5 pages long etc.
I'm not sure if it's something I've done that is causing this behaviour, i.e. always moving the signing section to the next page.
What I did:
I copied your code into the ThisWorkbook section and ran it manually to see how it would work.
Maybe the spreadsheet is holding onto some settings from run to run? Because as soon as I open the file now and print preview the signing section is automatically on page 2.
The form will be set up as a template, of length 1 page, -which will be the starting point for each use. It will then be saved with the data as a different form.

One other part that didn't work is resetting the form to the OriginalView. It seems to stay in xlPageBreakView even though the original view is xlNormalView.
I understand why you did it this way; regardless of what the original view is it will always return to that view.
I overcame this by directly setting the view to xlNormalView at the end rather than using your OriginalView routine. I'll add the code as it looks now below for reference.

A question:
When this is in use the user will simply want to fill in the data and print the form.
Where should I put your code in order to facilitate this?
I was thinking of restricting normal printing and putting a PRINT button on the form. Then I could put the code in a BeforePrint event on the Worksheet.
But if there is a simpler way I'd be delighted to hear it.

Public Sub Move_Last_Page_Break3()

    'Dim originalView
    Dim signoffEndCell As Range
    Dim lastHPageBreak As HPageBreak
    
    'Save current view and change it to Page Break Preview
    
'    With ActiveWindow
'        originalView = .View
'        .View = xlPageBreakPreview
'    End With

    'Look on the active sheet in the active workbook
    
    With ActiveWorkbook.ActiveSheet
                
        MsgBox .HPageBreaks(.HPageBreaks.Count).Location.Row
                
        'Find last signoff row
        
        Set signoffEndCell = .Columns("A").Find(What:="SIGNOFF", After:=.Range("A1"), LookIn:=xlFormulas2, LookAt:=xlPart, _
                                                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        'If the last horizontal page break occurs within the 7 signoff rows move it above first signoff row
        
        If .HPageBreaks(.HPageBreaks.Count).Location.Row > signoffEndCell.Row - 6 Then
            .HPageBreaks(.HPageBreaks.Count).Delete
            .HPageBreaks.Add Before:=signoffEndCell.Offset(-6)
        End If
                                
    End With
    
    'Restore original view
    
    ActiveWindow.View = xlNormalView 'originalView
    
End Sub

 

[John_w]

Can you post a couple of example sheets, using XL2BB - Excel Range to BBCode, one where the signing section needs to move and one where it doesn't.

 

[sparky2205]

Hi John, I'm trying to upload a few examples but I'm having a problem with character restrictions.
Example 1: The data should print on one page with the signing section appended at the bottom of the data.
Example 2: The data should print on page 1 and the signing section on page 2. (This will be in a new message below, I hope)

Example 1:

Form-0683-(LIVE).xlsm
	A	B	C	D	E	F	G	H	I	J
1	Wall Thickness & Concentricity Measurement Report
2	Instuctions: (i)Open the mastercopy of this form only and and "save as" with both the part number and the unique lot number as filename; e.g. "331561-1750012", in the appropriate project/customer folder on a shared drive (ii) All white boxes are to be completed or have " N/A" entered (iii) The number of decimal places required for each spec being measured should be entered under the min and max specs as per the specification on the front of the work order. Enter "1" for one decimal place, "2" for two decimal places and so on. (iv)Red cells under Bag # indicate a missing bag number. (v) Any red font indicates an out of spec measurement. (vi) The 1st sample number must be entered, this will usually be "1" but may be "81" if a second form is needed for example
3	Part No.				Lot No.			Customer
4	Min Sample Size:			Sample Qty:		0	Wall mm	Conc %	Conc mm	ID mm
5	Specification					Min	n/a	n/a	0.000	n/a
6						Max	n/a	100%	n/a	n/a
7	Purpose of form:				Decimal Places		3
8	Bag#	Sample #	Wall 1	Wall 2	Wall 3	Wall 4	Avg Wall	Conc %	Conc mm	ID
9	n/a		n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a
10	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a
11	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a
12	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a
13	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a
14						> Spec	0	0	0	0
15						< Spec	0	0	0	0
16	Gauge used						Data entered by
17	Description		Gauge #	Calibration due date (ddmmmyy)			Name	Signature		Date (ddmmmyy)
18
19							n/a	n/a		n/a
20							Data entered verified by
21
22	SIGNOFF						n/a	n/a		n/a
DATA

Cell Formulas
Range	Formula
F4	F4	=COUNTIFS($B$9:$B12,"<>n/a",$B$9:$B12,"<>"&"")
A9:A13	A9	=IF(G9="n/a","n/a","")
G9:G13	G9	=IF(C9="n/a","n/a",AVERAGE(C9:F9))
H9:H13	H9	=IF($H$5="n/a","n/a",IF(G9="n/a","n/a",((MIN(C9:F9))/(MAX(C9:F9)))))
I9:I13	I9	=IF($I$6="n/a","n/a",IF(G9="n/a","n/a",(MAX(C9:F9))-(MIN(C9:F9))))
B10:B13	B10	=IF(A10="n/a","n/a",B9+1)
G14	G14	=SUM(IF(ISNUMBER($G$9:$G13),IF(ROUND($G$9:$G13,$G$7)>$G$6,1)))
H14	H14	=SUM(IF(ISNUMBER($H$9:$H13),IF(ROUND($H$9:$H13,$G$7)>$H$6,1)))
I14	I14	=SUM(IF(ISNUMBER($I$9:$I13),IF(ROUND($I$9:$I13,$G$7)>$I$6,1)))
J14	J14	=SUM(IF(ISNUMBER($J$9:$J13),IF(ROUND($J$9:$J13,$G$7)>$J$6,1)))
G15	G15	=SUM(IF(ISNUMBER($G$9:$G13),IF(ROUND($G$9:$G13,$G$7)<$G$5,1)))
H15	H15	=SUM(IF(ISNUMBER($H$9:$H13),IF(ROUND($H$9:$H13,$G$7)<$H$5,1)))
I15	I15	=SUM(IF(ISNUMBER($I$9:$I13),IF(ROUND($I$9:$I13,$G$7)<$I$5,1)))
J15	J15	=SUM(IF(ISNUMBER($J$9:$J13),IF(ROUND($J$9:$J13,$G$7)<$J$5,1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A13:J13	Cell Value	="n/a"	text	YES
J13	Cell Value	>$J$6	text	NO
G13	Cell Value	<$G$5	text	NO
G13	Cell Value	>$G$6	text	NO
J13	Cell Value	<$J$5	text	NO
I13	Cell Value	>$I$6	text	NO
I13:J13,C13:G13	Expression	=$G$7=1	text	NO
I13:J13,C13:G13	Expression	=$G$7=2	text	NO
I13:J13,C13:G13	Expression	=$G$7=3	text	NO
I13:J13,C13:G13	Expression	=$G$7=4	text	NO
I13:J13,C13:G13	Expression	=$G$7=5	text	NO
I13:J13,C13:G13	Expression	=$G$7=6	text	NO
A13	Cell Value	=""	text	NO
H13	Cell Value	<$H$5	text	NO
D18:E18	Expression	=AND($D18<>"",$D18<TODAY())	text	NO
G14:J15	Cell Value	<>0	text	NO
G5:G6	Cell Value	="n/a"	text	YES
I6	Cell Value	="n/a"	text	YES
J5:J6	Cell Value	="n/a"	text	YES
F14:F15,A9:J12	Cell Value	="n/a"	text	YES
J9:J12	Cell Value	>$J$6	text	NO
G9:G12	Cell Value	<$G$5	text	NO
G9:G12	Cell Value	>$G$6	text	NO
J9:J12	Cell Value	<$J$5	text	NO
I9:I12	Cell Value	>$I$6	text	NO
I5:J6,G5:G6,I9:J12,F14:F15,C9:G12	Expression	=$G$7=1	text	NO
I5:J6,G5:G6,I9:J12,F14:F15,C9:G12	Expression	=$G$7=2	text	NO
I5:J6,G5:G6,I9:J12,F14:F15,C9:G12	Expression	=$G$7=3	text	NO
I5:J6,G5:G6,I9:J12,F14:F15,C9:G12	Expression	=$G$7=4	text	NO
I5:J6,G5:G6,I9:J12,F14:F15,C9:G12	Expression	=$G$7=5	text	NO
I5:J6,G5:G6,I9:J12,F14:F15,C9:G12	Expression	=$G$7=6	text	NO
A9:A12	Cell Value	=""	text	NO
H9:H12	Cell Value	<$H$5	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C7:D7	List	Usg Calibration, In-process Insp, Final Inspection
F14:F15	Any value
G7	List	0,1,2,3,4,5,6
D18:E18	Date	between 01/01/1900 and 01/01/2099
C9:F13	Custom	=OR(ISNUMBER(C9),C9="n/a")

 

[sparky2205]

John,
even stripping out all the formulas etc. with the extra lines the max character limit is exceeded.
Can you work with what I've already sent? Just add extra lines to force the scenarios we want to check.
I don't know exactly how XL2BB works, if it copies macros etc. but the spreadsheet does contain a macro to add rows.
If you need the password it's Test.

 

[John_w]

A question:
When this is in use the user will simply want to fill in the data and print the form.
Where should I put your code in order to facilitate this?
I was thinking of restricting normal printing and putting a PRINT button on the form. Then I could put the code in a BeforePrint event on the Worksheet.

Put the code in a standard module. You can then call it from a button/shape or another macro or an event handler.

There isn't a BeforePrint event for a worksheet, only Workbook_BeforePrint for the workbook, and I don't know if it can tell if a specific sheet is being printed.

Can you work with what I've already sent? Just add extra lines to force the scenarios we want to check.

Yes, I can work with your Example 1, and for Example 2 I added extra rows to force the 7 signoff rows to be split between 2 pages. If it's easier, upload your workbook to a file sharing site and post the link to it.

A small tweak was needed to my macro because Example 1 is only 1 page and therefore there are no horizontal page breaks. Here is the modified macro which now moves the last page break if there is at least 1 page break. I also changed LookIn:=xlFormulas2 to LookIn:=xlFormulas.

Public Sub Move_Last_Page_Break3()

    Dim originalView As XlWindowView
    Dim signoffEndCell As Range
       
    'Save current view and change it to Page Break Preview
   
    With ActiveWindow
        originalView = .View
        .View = xlPageBreakPreview
    End With

    'Look on the active sheet in the active workbook
   
    With ActiveWorkbook.ActiveSheet
       
        'Find last signoff row
       
        Set signoffEndCell = .Columns("A").Find(What:="SIGNOFF", After:=.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
                                                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        'If the last horizontal page break occurs within the 7 signoff rows move it above first signoff row
       
        If .HPageBreaks.Count > 0 Then
            If .HPageBreaks(.HPageBreaks.Count).Location.Row > signoffEndCell.Row - 6 Then
                .HPageBreaks(.HPageBreaks.Count).Delete
                .HPageBreaks.Add Before:=signoffEndCell.Offset(-6)
            End If
        End If
               
    End With
   
    'Restore original view
   
    ActiveWindow.View = originalView
   
End Sub

Here is Example 2 with the extra rows, before running the macro. It shows the signoff row on row 45 and a dashed blue line, indicating an automatic page break, on row 42.

After running the macro, the automatic page break has been changed to a manual page break, indicated by the solid blue line, on row 39, with the 7 signoff rows on Page 2.

 

[sparky2205]

Hi John,
apologies again for my late reply. I'm having a bit of a tough time with my recovery from Covid.
The attached code works perfectly for the two scenarios described:
• print all on page 1 if it fits
• move the signoff section to page 2 if it doesn't fit
But it also needs to print at the end of data on subsequent sheets if it fits. The new code prints the signoff section at the end of sheet 1 it it fits and moves the signoff section to sheet 2 if it doesn't fit. That's fine.
But if we take that further; if the signoff section fits at the end of the data on sheet 2 it should print on sheet 2. If it doesn't fit it should be moved to sheet 3. Currently the signoff section gets moved to sheet 3 regardless. This should be the same for all sheets regardless of how many sheets there are in the report.
e.g. if the report expands to 7 pages then if there is room for the signoff section at the end of page 7 it should print on page 7. Otherwise move the signoff section to page 8.

I hope that's clear and thanks for bearing with me. Looks like it's almost there.

 

[John_w]

Your description is very clear and I think I understand what you want, so I'm not sure why the macro isn't working for all cases. It doesn't matter how many pages are on the sheet because the macro looks for the last horizontal page break and moves it above the 7 signoff rows only if it occurs within the 7 signoff rows.

Try this modified macro which has some MsgBox debugging to show what it thinks is the last horizontal break break and the last signoff row and whether or not it moves the last horizontal page break.

Public Sub Move_Last_Page_Break3()

    Dim originalView As XlWindowView
    Dim signoffEndCell As Range
        
    'Save current view and change it to Page Break Preview
    
    With ActiveWindow
        originalView = .View
        .View = xlPageBreakPreview
    End With

    'Look on the active sheet in the active workbook
    
    With ActiveWorkbook.ActiveSheet
        
        'Find last signoff row
        
        Set signoffEndCell = .Columns("A").Find(What:="SIGNOFF", After:=.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
                                                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        'If the last horizontal page break occurs within the 7 signoff rows move it above first signoff row
        
        If .HPageBreaks.Count > 0 Then
            MsgBox "Number of horizontal page breaks = " & .HPageBreaks.Count & vbCrLf & _
                   "Last horizontal page break row = " & .HPageBreaks(.HPageBreaks.Count).Location.Row & vbCrLf & _
                   "Last signoff row = " & signoffEndCell.Row
            If .HPageBreaks(.HPageBreaks.Count).Location.Row > signoffEndCell.Row - 6 Then
                .HPageBreaks(.HPageBreaks.Count).Delete
                .HPageBreaks.Add Before:=signoffEndCell.Offset(-6)
                MsgBox "Last horizontal page break moved to row " & .HPageBreaks(.HPageBreaks.Count).Location.Row
            Else
                MsgBox "Last horizontal page break not moved"
            End If
        End If
                
    End With
    
    'Restore original view
    
    ActiveWindow.View = originalView
    
End Sub