Print to PDF > new file every time there is a page break?

[xbrianx]

Thanks to VoG, I now know how to create an auto page break based on when then the value changes in column 'A' by using Microsoft Visual Basic with the following code:

Sub x()
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 2 Step -1
If Range("A" & i).Value <> Range("A" & i - 1).Value Then
ActiveSheet.HPageBreaks.Add Before:=Range("A" & i)
End If
Next i
End Sub

I'd like to take this a step futher and Print to PDF (via Acrobat) based on every time there is a page break and save the pdf files as different file names. Here is a snippet of my data:

<table border="0" cellpadding="0" cellspacing="0" width="257"><col style="width: 29pt;" width="39"> <col style="width: 75pt;" width="100"> <col style="width: 89pt;" width="118"> <tbody><tr style="height: 22.5pt;" height="30"> <td class="xl67" style="height: 22.5pt; width: 29pt; font-size: 10pt; color: white; font-weight: 700; text-decoration: none; font-family: Arial; border-width: 0.5pt 0.5pt 1pt; border-style: solid; border-color: black; background: none repeat scroll 0% 0% black;" width="39" height="30">SITE</td> <td class="xl65" style="width: 75pt; font-size: 10pt; color: white; font-weight: 700; text-decoration: none; font-family: Arial; border-width: 0.5pt 0.5pt 1pt medium; border-style: solid solid solid none; border-color: black black black -moz-use-text-color; background: none repeat scroll 0% 0% black;" width="100">FIRST</td> <td class="xl65" style="width: 89pt; font-size: 10pt; color: white; font-weight: 700; text-decoration: none; font-family: Arial; border-width: 0.5pt 0.5pt 1pt medium; border-style: solid solid solid none; border-color: black black black -moz-use-text-color; background: none repeat scroll 0% 0% black;" width="118">LAST</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">001</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">TONY</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">BALDWIN</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">001</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">MEREDITH</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">CASTELLOE</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">001</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">SHARON</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">FISH</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">001</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">CONSTANCE</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">JACKSON</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">001</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">DONNA</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">LANAHAN</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">001</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">ROBIN</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">MEHLER</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">TINA</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">BUCKLEY</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">BARBARA</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">HARRISON</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">ROBYN</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">HESS</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">JUNE</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">INGLE</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">CYNTHIA</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">LOPEZ</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">CAROLYN</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">MASHBURN</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">ANITA</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">PROFFITT</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">BRIAN</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">PROPST</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">CHERYL</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">RYBACKI</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">RON</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">VENTURELLA</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">002</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">F.</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">WELLER</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">003</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">GREENLEE</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">CALDWELL</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(216, 216, 216);" height="30">003</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">JOHN</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216);">CARRIERE</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl68" style="height: 22.5pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color black black;" height="30">003</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">SAMMY</td> <td class="xl66" style="font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color;">CLEMMONS</td> </tr> </tbody></table>
So basically I want the 1st PDF to be named "001" and the 2nd PDF to be names "002" ... etc. This way I don't have to use Acrobat to split out these files based on the page breaks individually. Thanks in advance for any help you can provide.

 

[xbrianx]

bump

 

[Lizabeta]

Bump again, this is exactly what I need too!

 

[Lizabeta]

My own (non VBA, not entirely satisfactory but better than nothing) answer:

If you have Acrobat Professional (or can download the free trial) you can Print to PDF (using page breaks to create separate pages in the PDF File).

Then open it in Acrobat Professional. Go to Document: Extract pages. Choose Extract Pages as separate files. Enter your page range. Click ok. Select the location you want them all to save.

Biggest drawback, the files aren't named on any criteria.

But they are all separate files.

 

[xbrianx]

Lizabeta... yes, I've used Acrobat to do exactly as you've described but you you've figured out too, you can't assign a naming pattern to it... it's just page 0001, 0002, 0003, etc. Thanks for the post though!

 

[Lizabeta]

I would still very much like to find a way to code each page to name separately. It was just my 'best effort' so far... since I hadn't seen even that much of solution anywhere else.