Custom Page Break for Last Page and Print Last page without Row Headers

[hassanleo1987]

Hi,

I have a multi-sheet file where I need custom page break based on pivot table refresh and last page of every sheet must be printed without repeated row headers.

Step-1: Change the date in Cell H2 and Refresh the pivot table. This will update the Data Table A5:I70 where are all cells are formulated from pivot table cache table. (Automatic calculation should be checked before this step in order to update the data table quickly)

Step-2: After refreshing the pivot table, check the formula blank rows at the end of data table and delete all the formula blank rows (marked "Delete row" here in the example), leaving last 2 rows of data table intact.

Step-3: The automatic page break will shift after deleting the formula blank rows. Check if the automatic page break is coming the in the fixed Range (A47:I63).

Step-4: If NO, then OK to save the file as PDF with current page setup / print area. (This way the fixed Range (A47:I63) comes nicely under the data table!)

Step-5: If YES, then move the last page break above ROW 47. remove the repeat row header only for the last page and save the file as PDF. (Since the actual data table will be a multipage setup).

As I have mentioned this is multisheet workbook but for the sake of example, here it is showing only one.

I can modify to added other sheets as required. because the step 2 ~ 5 will be repeated for next sheets and after the last specified sheet, an array of selected sheets wil be grouped and the file will be saved as PDF. (This I can handle myself!)

Please help with step 2 ~ 5 code!

Here is the example sheet.

Example TS.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	1
2	2						Date :	1-Oct
3	3
4	A	B	C	D	E	F	G	H	I			Row Labels
5	10	abc	abc	abc	abc	abc	abc	10	abc			10
6	20	abc	abc	abc	abc	abc	abc	20	abc			20
7	30	abc	abc	abc	abc	abc	abc	30	abc			30
8	40	abc	abc	abc	abc	abc	abc	40	abc			40
9	50	abc	abc	abc	abc	abc	abc	50	abc			50
10	60	abc	abc	abc	abc	abc	abc	60	abc			60
11	70	abc	abc	abc	abc	abc	abc	70	abc			70
12	1170	abc	abc	abc	abc	abc	abc	1170	abc			1170
13	1180	abc	abc	abc	abc	abc	abc	1180	abc			1180
14	1190	abc	abc	abc	abc	abc	abc	1190	abc			1190
15	1200	abc	abc	abc	abc	abc	abc	1200	abc			1200
16	1210	abc	abc	abc	abc	abc	abc	1210	abc			1210
17	1220	abc	abc	abc	abc	abc	abc	1220	abc			1220
18	1230	abc	abc	abc	abc	abc	abc	1230	abc			1230
19	1240	abc	abc	abc	abc	abc	abc	1240	abc			1240
20	1250	abc	abc	abc	abc	abc	abc	1250	abc			1250
21	1260	abc	abc	abc	abc	abc	abc	1260	abc			1260
22	1270	abc	abc	abc	abc	abc	abc	1270	abc			1270
23	1280	abc	abc	abc	abc	abc	abc	1280	abc			1280
24	1290	abc	abc	abc	abc	abc	abc	1290	abc			1290
25	1300	abc	abc	abc	abc	abc	abc	1300	abc			1300
26	1310	abc	abc	abc	abc	abc	abc	1310	abc			1310
27	1320	abc	abc	abc	abc	abc	abc	1320	abc			1320
28	1330	abc	abc	abc	abc	abc	abc	1330	abc			1330
29	1340	abc	abc	abc	abc	abc	abc	1340	abc			1340
30	1350	abc	abc	abc	abc	abc	abc	1350	abc			1350
31	1360	abc	abc	abc	abc	abc	abc	1360	abc			1360
32	1370	abc	abc	abc	abc	abc	abc	1370	abc			1370
33	1380	abc	abc	abc	abc	abc	abc	1380	abc			1380
34	1390	abc	abc	abc	abc	abc	abc	1390	abc			1390
35	1400	abc	abc	abc	abc	abc	abc	1400	abc			1400
36	1410	abc	abc	abc	abc	abc	abc	1410	abc			1410
37	1420	abc	abc	abc	abc	abc	abc	1420	abc			1420
38	1430	abc	abc	abc	abc	abc	abc	1430	abc			1430
39	1440	abc	abc	abc	abc	abc	abc	1440	abc			1440
40	1450	abc	abc	abc	abc	abc	abc	1450	abc			1450
41	1460	abc	abc	abc	abc	abc	abc	1460	abc			1460
42	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
43									Delete Row
44									Delete Row
45									Delete Row
46									Delete Row
47									Delete Row
48									Delete Row
49									Delete Row
50									Delete Row
51									Delete Row
52									Delete Row
53									Delete Row
54									Delete Row
55									Delete Row
56									Delete Row
57									Delete Row
58									Delete Row
59									Delete Row
60									Delete Row
61									Delete Row
62									Delete Row
63									Delete Row
64									Delete Row
65									Delete Row
66									Delete Row
67									Delete Row
68									Delete Row
69
70
71					Total			41200
72
73	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
74	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
75	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
76	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
77	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
78	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
79	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
80	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
81	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
82	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
83	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
84	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
85	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
86	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
87	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
88	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
89	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz	xyz
90
Sheet1

Cell Formulas
Range	Formula
H71	H71	=SUM(H5:H70)

 

[hassanleo1987]

@Akuini , Need you help! Please take a look!

 

[Akuini]

@Akuini , Need you help! Please take a look!

Sorry, I can't help you with this.
Hope someone else can.

 

[hassanleo1987]

Sorry, I can't help you with this.
Hope someone else can.

Example TS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	1
2	2						Date :	1-Oct
3	3
4	A	B	C	D	E	F	G	H	I			Row Labels
5	10	abc	abc	abc	abc	abc	abc	10	abc			10
6	20	abc	abc	abc	abc	abc	abc	20	abc			20
7	30	abc	abc	abc	abc	abc	abc	30	abc			30
8	40	abc	abc	abc	abc	abc	abc	40	abc			40
9	50	abc	abc	abc	abc	abc	abc	50	abc			50
10	60	abc	abc	abc	abc	abc	abc	60	abc			60
11	70	abc	abc	abc	abc	abc	abc	70	abc			70
12	1170	abc	abc	abc	abc	abc	abc	1170	abc			1170
13	1180	abc	abc	abc	abc	abc	abc	1180	abc			1180
14	1190	abc	abc	abc	abc	abc	abc	1190	abc			1190
15	1200	abc	abc	abc	abc	abc	abc	1200	abc			1200
16	1210	abc	abc	abc	abc	abc	abc	1210	abc			1210
17	1220	abc	abc	abc	abc	abc	abc	1220	abc			1220
18	1230	abc	abc	abc	abc	abc	abc	1230	abc			1230
19	1240	abc	abc	abc	abc	abc	abc	1240	abc			1240
20	1250	abc	abc	abc	abc	abc	abc	1250	abc			1250
21	1260	abc	abc	abc	abc	abc	abc	1260	abc			1260
22	1270	abc	abc	abc	abc	abc	abc	1270	abc			1270
23	1280	abc	abc	abc	abc	abc	abc	1280	abc			1280
24	1290	abc	abc	abc	abc	abc	abc	1290	abc			1290
25	1300	abc	abc	abc	abc	abc	abc	1300	abc			1300
26	1310	abc	abc	abc	abc	abc	abc	1310	abc			1310
27	1320	abc	abc	abc	abc	abc	abc	1320	abc			1320
28	1330	abc	abc	abc	abc	abc	abc	1330	abc			1330
29	1340	abc	abc	abc	abc	abc	abc	1340	abc			1340
30	1350	abc	abc	abc	abc	abc	abc	1350	abc			1350
31	1360	abc	abc	abc	abc	abc	abc	1360	abc			1360
32	1370	abc	abc	abc	abc	abc	abc	1370	abc			1370
33	1380	abc	abc	abc	abc	abc	abc	1380	abc			1380
34	1390	abc	abc	abc	abc	abc	abc	1390	abc			1390
35	1400	abc	abc	abc	abc	abc	abc	1400	abc			1400
36	1410	abc	abc	abc	abc	abc	abc	1410	abc			1410
37	1420	abc	abc	abc	abc	abc	abc	1420	abc			1420
38	1430	abc	abc	abc	abc	abc	abc	1430	abc			1430
39	1440	abc	abc	abc	abc	abc	abc	1440	abc			1440
40	1450	abc	abc	abc	abc	abc	abc	1450	abc			1450
41	1460	abc	abc	abc	abc	abc	abc	1460	abc			1460
42	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
43	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
44	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
45	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
46	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
47	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
48	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
49	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
50	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
51	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
52	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
53	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
54	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
55	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
56	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
57	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
58	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
59	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
60	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
61	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
62	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
63	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
64	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
65	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
66	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
67	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
68	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
69	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
70	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
71	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
72	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
73	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
74	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
75	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
76	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
77	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
78	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
79	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
80	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
81	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
82	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
83	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
84	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
85	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
86	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
87	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
88	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
89	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
90	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
91	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
92	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
93	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
94	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
95	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
96	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
97	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
98	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
99	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
100	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
101	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
102	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
103	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
104	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
105	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
106	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
107	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
108	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
109	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
110	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
111	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
112	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
113	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
114	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
115	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
116	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
117	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
118	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
119	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
120	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
121	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
122	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
123	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
124	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
125	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
126	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
127	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
128	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
129	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
130	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
131	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
132	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
133	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
134	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
135	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
136	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
137	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
138	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
139	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
140	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
141	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
142	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
143	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
144	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
145	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
146	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
147	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
148	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
149	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
150	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
151	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
152	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
153	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
154	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
155	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
156	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
157	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
158	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
159	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
160	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
161	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
162	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
163
164
165
166
167
168
169
170
171
172
173
174
175												a
176												a
177
178
179	X	X	X	X	X	X	X	X	X	PB
180	X	X	X	X	X	X	X	X	X
181	X	X	X	X	X	X	X	X	X
182	X	X	X	X	X	X	X	X	X
183	X	X	X	X	X	X	X	X	X
184	X	X	X	X	X	X	X	X	X
185	X	X	X	X	X	X	X	X	X
186	X	X	X	X	X	X	X	X	X
187	X	X	X	X	X	X	X	X	X
188	X	X	X	X	X	X	X	X	X
189	X	X	X	X	X	X	X	X	X
190	X	X	X	X	X	X	X	X	X
191	X	X	X	X	X	X	X	X	X
192	X	X	X	X	X	X	X	X	X
193	X	X	X	X	X	X	X	X	X
194	X	X	X	X	X	X	X	X	X
195	X	X	X	X	X	X	X	X	X
196	X	X	X	X	X	X	X	X	X
197	X	X	X	X	X	X	X	X	X
KKK

For this example, I have worked out some code. It was working just as needed but after I changed the file location, now its not saving the pdf files as intended.

Can somebody please take a look and help me find what I am doing wrong!

Sub Page_Formatting()
Dim i As Range, C As Range, K As Range, J As Range
Dim active_sheet As Variant, page_nums As Variant
Dim sCurrentPrinter As String, sPDFwriter As String
Dim strPath As String

MyPrinter = FindPrinter("Microsoft Print to PDF")

sPDFwriter = MyPrinter

    sCurrentPrinter = Application.ActivePrinter
    Application.ActivePrinter = sPDFwriter

    With Range("L4:L176").Select
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            Set i = Range("J:J")
            For Each C In i
            If C = "PB" Then
                ActiveSheet.Rows(C.Row).PageBreak = xlPageBreakManual
            End If
        Next C
    End With

Set active_sheet = ThisWorkbook.ActiveSheet
page_nums = active_sheet.PageSetup.Pages.Count

    On Error Resume Next
    Application.PrintCommunication = True
    Err.Clear

    With ActiveSheet.PageSetup
    .PrintTitleRows = "$4:$4"
    ActiveSheet.PrintOut From:=1, To:=page_nums - 1, PrintToFile:=True, PrToFileName:=".pdf"
    .PrintTitleRows = ""
    ActiveSheet.PrintOut From:=page_nums, To:=page_nums, PrintToFile:=True, PrToFileName:="B.pdf"
    End With

End Sub

Function FindPrinter(ByVal PrinterName As String) As String


  Dim Arr As Variant
  Dim Device As Variant
  Dim Devices As Variant
  Dim Printer As String
  Dim RegObj As Object
  Dim RegValue As String
  Const HKEY_CURRENT_USER = &H80000001
     
    Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    RegObj.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr
   
      For Each Device In Devices
        RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
        Printer = Device & " on " & Split(RegValue, ",")(1)
        If InStr(1, Printer, PrinterName, vbTextCompare) > 0 Then
           FindPrinter = Printer
           Exit Function
        End If
      Next
     
End Function

 

[hassanleo1987]

This code is not perfect or optimized as it should be! So if you have suggestion to optimize it, please do share.

With Range("L4:L176").Select
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

This can be optimized to select the top cell and select till the last non blank cell of the column. I have tried different methods I found but can't seem to get it to work.

Set i = Range("J:J")
            For Each C In i
            If C = "PB" Then
                ActiveSheet.Rows(C.Row).PageBreak = xlPageBreakManual
            End If
        Next C

This portion can also be optimized by rather checking every cell of the range, should be select the only cell with value in it!

@Peter_SSs , @Fluff Please have a look!

 

[hassanleo1987]

@Akuini I have finally worked out the code for solution!
Please take a look and see if you have help optimize it.
Especially the points I have highlighted earlier. The reason for that is, as I have mentioned, I will configure the code to work on multiple sheets with variable number of rows. currently this part takes time since it cycles through all cells of the range. That is why this should be optimized!

Set i = Range("J:J")
            For Each C In i
            If C = "PB" Then
                ActiveSheet.Rows(C.Row).PageBreak = xlPageBreakManual
            End If
        Next C

The second point of optimization is covered with fixed range but still can be simplified, I think!

With Range("L4:L176").Select
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Here is the example I had for the code!

Example TS - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	1
2	2						Date :	1-Oct
3	3
4	A	B	C	D	E	F	G	H	I			Row Labels
5	10	abc	abc	abc	abc	abc	abc	10	abc			10
6	20	abc	abc	abc	abc	abc	abc	20	abc			20
7	30	abc	abc	abc	abc	abc	abc	30	abc			30
8	40	abc	abc	abc	abc	abc	abc	40	abc			40
9	50	abc	abc	abc	abc	abc	abc	50	abc			50
10	60	abc	abc	abc	abc	abc	abc	60	abc			60
11	70	abc	abc	abc	abc	abc	abc	70	abc			70
12	1170	abc	abc	abc	abc	abc	abc	1170	abc			1170
13	1180	abc	abc	abc	abc	abc	abc	1180	abc			1180
14	1190	abc	abc	abc	abc	abc	abc	1190	abc			1190
15	1200	abc	abc	abc	abc	abc	abc	1200	abc			1200
16	1210	abc	abc	abc	abc	abc	abc	1210	abc			1210
17	1220	abc	abc	abc	abc	abc	abc	1220	abc			1220
18	1230	abc	abc	abc	abc	abc	abc	1230	abc			1230
19	1240	abc	abc	abc	abc	abc	abc	1240	abc			1240
20	1250	abc	abc	abc	abc	abc	abc	1250	abc			1250
21	1260	abc	abc	abc	abc	abc	abc	1260	abc			1260
22	1270	abc	abc	abc	abc	abc	abc	1270	abc			1270
23	1280	abc	abc	abc	abc	abc	abc	1280	abc			1280
24	1290	abc	abc	abc	abc	abc	abc	1290	abc			1290
25	1300	abc	abc	abc	abc	abc	abc	1300	abc			1300
26	1310	abc	abc	abc	abc	abc	abc	1310	abc			1310
27	1320	abc	abc	abc	abc	abc	abc	1320	abc			1320
28	1330	abc	abc	abc	abc	abc	abc	1330	abc			1330
29	1340	abc	abc	abc	abc	abc	abc	1340	abc			1340
30	1350	abc	abc	abc	abc	abc	abc	1350	abc			1350
31	1360	abc	abc	abc	abc	abc	abc	1360	abc			1360
32	1370	abc	abc	abc	abc	abc	abc	1370	abc			1370
33	1380	abc	abc	abc	abc	abc	abc	1380	abc			1380
34	1390	abc	abc	abc	abc	abc	abc	1390	abc			1390
35	1400	abc	abc	abc	abc	abc	abc	1400	abc			1400
36	1410	abc	abc	abc	abc	abc	abc	1410	abc			1410
37	1420	abc	abc	abc	abc	abc	abc	1420	abc			1420
38	1430	abc	abc	abc	abc	abc	abc	1430	abc			1430
39	1440	abc	abc	abc	abc	abc	abc	1440	abc			1440
40	1450	abc	abc	abc	abc	abc	abc	1450	abc			1450
41	1460	abc	abc	abc	abc	abc	abc	1460	abc			1460
42	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
43	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
44	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
45	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
46	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
47	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
48	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
49	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
50	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
51	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
52	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
53	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
54	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
55	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
56	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
57	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
58	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
59	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
60	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
61	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
62	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
63	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
64	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
65	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
66	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
67	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
68	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
69	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
70	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
71	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
72	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
73	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
74	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
75	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
76	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
77	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
78	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
79	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
80	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
81	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
82	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
83	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
84	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
85	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
86	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
87	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
88	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
89	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
90	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
91	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
92	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
93	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
94	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
95	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
96	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
97	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
98	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
99	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
100	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
101	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
102	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
103	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
104	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
105	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
106	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
107	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
108	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
109	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
110	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
111	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
112	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
113	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
114	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
115	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
116	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
117	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
118	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
119	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
120	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
121	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
122	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
123	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
124	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
125	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
126	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
127	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
128	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
129	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
130	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
131	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
132	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
133	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
134	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
135	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
136	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
137	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
138	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
139	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
140	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
141	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
142	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
143	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
144	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
145	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
146	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
147	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
148	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
149	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
150	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
151	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
152	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
153	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
154	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
155	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
156	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
157	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
158	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
159	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
160	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
161	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
162	1470	abc	abc	abc	abc	abc	abc	1470	abc			1470
163
164
165
166
167
168
169
170
171
172
173
174
175												a
176												a
177
178
179	X	X	X	X	X	X	X	X	X	PB
180	X	X	X	X	X	X	X	X	X
181	X	X	X	X	X	X	X	X	X
182	X	X	X	X	X	X	X	X	X
183	X	X	X	X	X	X	X	X	X
184	X	X	X	X	X	X	X	X	X
185	X	X	X	X	X	X	X	X	X
186	X	X	X	X	X	X	X	X	X
187	X	X	X	X	X	X	X	X	X
188	X	X	X	X	X	X	X	X	X
189	X	X	X	X	X	X	X	X	X
190	X	X	X	X	X	X	X	X	X
191	X	X	X	X	X	X	X	X	X
192	X	X	X	X	X	X	X	X	X
193	X	X	X	X	X	X	X	X	X
194	X	X	X	X	X	X	X	X	X
195	X	X	X	X	X	X	X	X	X
196	X	X	X	X	X	X	X	X	X
197	X	X	X	X	X	X	X	X	X
KKK

If you run the Page_Formatting Macro, It will produce 3 PDF files. Part 1 and 2 are just to ensure repeat row header is not repeated on last page and then call another macro to merg the PDF files in the folder location.

Sub Page_Formatting()
Dim i As Range, C As Range, K As Range, J As Range
Dim active_sheet As Variant, page_nums As Variant
Dim sCurrentPrinter As String, sPDFwriter As String
Dim File1 As String, File2 As String

File1 = ActiveWorkbook.Path & "\Part - 1.pdf"
File2 = ActiveWorkbook.Path & "\Part - 2.pdf"


MyPrinter = FindPrinter("Microsoft Print to PDF")

sPDFwriter = MyPrinter

    sCurrentPrinter = Application.ActivePrinter
    Application.ActivePrinter = sPDFwriter

    With Range("L4:L176").Select
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            Set i = Range("J:J")
            For Each C In i
            If C = "PB" Then
                ActiveSheet.Rows(C.Row).PageBreak = xlPageBreakManual
            End If
        Next C
    End With

Set active_sheet = ThisWorkbook.ActiveSheet
page_nums = active_sheet.PageSetup.Pages.Count

    On Error Resume Next
    Application.PrintCommunication = True
    Err.Clear

    With ActiveSheet.PageSetup
    .PrintTitleRows = "$4:$4"
    ActiveSheet.PrintOut From:=1, To:=page_nums - 1, PrintToFile:=True, PrToFileName:=File1
    .PrintTitleRows = ""
    ActiveSheet.PrintOut From:=page_nums, To:=page_nums, PrintToFile:=True, PrToFileName:=File2
    End With
Call MergePDF
End Sub

Function FindPrinter(ByVal PrinterName As String) As String

  Dim Arr As Variant
  Dim Device As Variant
  Dim Devices As Variant
  Dim Printer As String
  Dim RegObj As Object
  Dim RegValue As String
  Const HKEY_CURRENT_USER = &H80000001
      
    Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    RegObj.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr
    
      For Each Device In Devices
        RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
        Printer = Device & " on " & Split(RegValue, ",")(1)
        If InStr(1, Printer, PrinterName, vbTextCompare) > 0 Then
           FindPrinter = Printer
           Exit Function
        End If
      Next
End Function

Sub MergePDF()

Dim n As Long, PDFfileName As String
'Relies on the Adobe Acrobat 6.0 Type Library
Dim objCAcroPDDocDestination As Acrobat.CAcroPDDoc
Dim objCAcroPDDocSource As Acrobat.CAcroPDDoc

'Initialize the objects
Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")

'Open Destination, all other documents will be added to this and saved with
'a new filename
objCAcroPDDocDestination.Open (ThisWorkbook.Path & "\Part - 1.pdf")
n = 1
    Do
        n = n + 1
        PDFfileName = Dir(ThisWorkbook.Path & "\Part - " & n & ".pdf")
        If PDFfileName <> "" Then
            'Open the source document that will be added to the destination
            objCAcroPDDocSource.Open ThisWorkbook.Path & "\" & PDFfileName
            If objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
                MsgBox "Merged " & PDFfileName
            Else
                MsgBox "Error merging " & PDFfileName
            End If
            objCAcroPDDocSource.Close
        End If
    Loop While PDFfileName <> ""

objCAcroPDDocDestination.Save 1, ThisWorkbook.Path & "\Report (Complete).pdf"
objCAcroPDDocDestination.Close
Set objCAcroPDDocSource = Nothing
Set objCAcroPDDocDestination = Nothing

End Sub

@Akuini I would really appreciate if you could help optimize this code.

 

[Akuini]

This part will make the code loop on every cell in the whole column J.

            Set i = Range("J:J")
            For Each C In i

Change this part:

    With Range("L4:L176").Select
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            Set i = Range("J:J")
            For Each C In i
            If C = "PB" Then
                ActiveSheet.Rows(C.Row).PageBreak = xlPageBreakManual
            End If
        Next C
    End With

to this:

    Range("L4:L176").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    Dim sAddress As String
    With Range("J:J")
        Set c = .Find(What:="PB", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                sAddress = c.Address
                Do
                   c.EntireRow.PageBreak = xlPageBreakManual
                   Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> sAddress
            End If
    End With

Note: I didn't test the code, I just thought that your code could be revised like this to make it work more efficiently.

 

[hassanleo1987]

This part will make the code loop on every cell in the whole column J.

            Set i = Range("J:J")
            For Each C In i

Change this part:

    With Range("L4:L176").Select
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            Set i = Range("J:J")
            For Each C In i
            If C = "PB" Then
                ActiveSheet.Rows(C.Row).PageBreak = xlPageBreakManual
            End If
        Next C
    End With

to this:

    Range("L4:L176").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   
    Dim sAddress As String
    With Range("J:J")
        Set c = .Find(What:="PB", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                sAddress = c.Address
                Do
                   c.EntireRow.PageBreak = xlPageBreakManual
                   Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> sAddress
            End If
    End With

Note: I didn't test the code, I just thought that your code could be revised like this to make it work more efficiently.

Thanks for your insight, it will definitely test it out tomorrow and share the results. Current I have converted my code to work on 9 worksheets with variable heights (no of rows) ranging from 100 to 5000 rows each. And it takes about 110 sec to process through all of them.

This loop will definitely help reduce the time trying to find 1 value in whole colum.

Again thanks for your valuable input.

 

[hassanleo1987]

This part will make the code loop on every cell in the whole column J.

            Set i = Range("J:J")
            For Each C In i

Change this part:

    With Range("L4:L176").Select
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            Set i = Range("J:J")
            For Each C In i
            If C = "PB" Then
                ActiveSheet.Rows(C.Row).PageBreak = xlPageBreakManual
            End If
        Next C
    End With

to this:

    Range("L4:L176").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   
    Dim sAddress As String
    With Range("J:J")
        Set c = .Find(What:="PB", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                sAddress = c.Address
                Do
                   c.EntireRow.PageBreak = xlPageBreakManual
                   Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> sAddress
            End If
    End With

Note: I didn't test the code, I just thought that your code could be revised like this to make it work more efficiently.

@Akuini Tried your snippet and its great, reduces the overall time by 18 secs!

Again Thanks a lot for your time!