Export Individual PDFs based on Page Breaks (regardless of whether auto or manual)

trlear

New Member
Joined
Mar 26, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
A few years ago I found some VBA code on this site that saved me a ton of work!!! However, the data has reached the point where the data for each value might require two pages. I have a large spreadsheet that includes a macro that hides unnecessary columns, formats a few columns, repeats row 1 header on each page and creates a manual page break after each change of value in column A. (Ex. I have employee names in column A so the macro places a hard page break after each employee) In most cases the data for each employee is on one page, but there are times now where one employee might have more rows than will fit on a page so it will insert an auto (phantom) break.
I row one filtered and turn off what I don't want to see, and then run the macro below that exports each page to a PDF based on the page breaks and names it according to the employee name in column A. If there are two pages for one person it will name them John Smith_1 and John Smith_2 respectively. The issue is that it eventually errors out. It seems as though it gets to the auto page breaks and doesn't know what to do. It works on a few of them giving them separate names but then errors out. When I check the code, it highlights the code shown below in red bold. Can someone tell me what I'm doing wrong? I've tried running with and without the filters to see if that's the issues, but that doesn't seem to be the issue either. Any help appreciated.

Sub exportPages()

Set Sht = Worksheets("Sheet1")
ExportDir = "C:\temp\"
NrPages = Sht.HPageBreaks.Count + 1

For p = 1 To NrPages
If p = 1 Then
RwStart = 1
Else
RwStart = Sht.HPageBreaks(p - 1).Location.Row
End If
FoundName = Sht.Range("B" & RwStart).Value

ExportName = "Export_" & FoundName & "_" & p & ".pdf"
Sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ExportDir & ExportName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=p, to:=p, OpenAfterPublish:=False
Next

Set Sht = Nothing

End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,129,682
Messages
5,637,764
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top