Help With Dynamically Adding Page Breaks to a Dynamic Worksheet for Printing

[DrParmeJohnson]

Hello,
I'm working on this sheet, which the snippet is only a piece of, which is the result of a macro which determines the numbers of items that should be placed into a box from each of our respective PO's. The issue I'm trying to resolve is that this sheet often will have one of the PO Names on the end of the sheet at the bottom of a page (as we print this out as a light guide) and then it's items will get kicked onto the next page.

So like the bottom of this sheet, 1GBH7KII - AVP1 would be the last line and then Calcium Chloride - 2 oz. : LGJY would get pushed onto page 2 and it just makes things confusing.

The issue then that I'm trying to figure out how to fix is how to make this macro figure out if not the entire PO is on the page, insert a page break and kick the whole thing to the next one, thus keeping it one unit. I know that each printed sheet after my page formatting is about 46 rows at the most. I also am not sure if there is a way to figure out like a "printed page index" so you can keep track of which page the info is on.

So, if anyone has any ideas on how this may be able to be achieved or some other solution that has the same effect, I would greatly appreciate it.

Thanks,
DPJ

PurchaseOrderItems (10).csv
	A	B	C	D	E	F	G	H	I	J	K
1	Number To Go In Appropriate Box	45	70	63	12	37	90	39	120	36	4
2	117M8W7P - MDW2	45	70	63	12	37	90	39	120	36	4
3	Calcium Chloride - 2 oz. : LGJY
4	Calcium Carbonate - 1 lb. : KHLQ	4
5	Potassium Metabisulfite - 2 oz. : JQ7C
6	38mm Metal Caps - 12 ct. : J2Y6
7	Cider Yeast - 3 ct. : GUPW
8	Briess Maltoferm DME - 1 lb. : GBXQ
9	Gypsum- 1 lb. : GEZG	7
10	Campden Tablets SMS - 100 ct. : 0KC6
11	1C487V1F - MDW2	45	70	63	12	37	90	39	120	36	4
12	DADY - 1 lb. : NG8A	5
13	Cider Yeast - 3 ct. : GUPW		2	1
14	Corn Sugar - 1 lb. : YDD4
15	Corn Sugar - 4 lbs. : FVSW				5
16	Sparkolloid Powder - 1 lb. : ZQMG
17	#9 x 1 1/2" First Quality Corks - 100 ct. : VZCG
18	1GBH7KII - AVP1	45	70	63	12	37	90	39	120	36	4
19	Calcium Chloride - 2 oz. : LGJY
20	Calcium Carbonate - 1 lb. : KHLQ	6				1
Boxes for POs

 

[Domenic]

The following macro will first reset all page breaks, and then loop through each horizontal page break to make any necessary adjustments.

So, basically, for each horizontal page break, it checks whether the first item on the next page is a purchase order number. If not, it keeps checking the previous row until it finds one. Once it finds one, it moves the horizontal page break to that new location, and all subsequent horizontal page breaks are automatically re-calculated.

Note that when a single purchase order extends beyond one page, the horizontal page break will remain in place.

Also, note that the macro assumes that the sheet containing the data is the active sheet.

Option Explicit

Sub Adjust_HPageBreaks()

    ActiveSheet.ResetAllPageBreaks
    
    Dim view_type As Long
    view_type = ActiveWindow.View 'remember current view
    
    ActiveWindow.View = xlPageBreakPreview 'required to set new location for HPageBreak
    
    Dim hpagebreak_index As Long
    Dim current_location As Range
    Dim current_item As String
    
    hpagebreak_index = 1
    Do While hpagebreak_index <= ActiveSheet.HPageBreaks.Count
        Set current_location = ActiveSheet.HPageBreaks(hpagebreak_index).Location
        current_item = Replace(current_location.Value, " ", "")
        Do While (Not current_item Like "????????-????")
            Set current_location = current_location.Offset(-1, 0)
            current_item = Replace(current_location.Value, " ", "")
        Loop
        If current_location.Row > 2 Then
            Set ActiveSheet.HPageBreaks(hpagebreak_index).Location = current_location
        End If
        hpagebreak_index = hpagebreak_index + 1
    Loop
    
    ActiveWindow.View = view_type 'restore original view
    
End Sub

Hope this helps!

 

[DrParmeJohnson]

Changed it to reflect it being worksheet 3 (ws3) but otherwise works great. Only other weird thing is that my print preview won't load nor connect to my printer but I think that may be a separate issue.

Thank you again, I really appreciate it.

 

[Domenic]

That's great, glad I could help.

Cheers!