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
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
