Save each page break as seperate PDF

[DPhilip]

Guys,

I need your help. I am trying to save each Pagebreak in "Sheet 1" as separate files into a folder as PDF. The summary is running of a pivot so its dynamic depending on the number of people asking for payments. I would like to name each PDF based on the customer name given Column B first cell in each page break.

So far, I only have codes to export the entire file as PDF.

Sub SaveasPDF()



Dim filename As String
With Worksheets("My Sheet")
filename = Range("A1")
'& .Range("A2").Value & .Range("A3").Value
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
"C:\Users\DPhilip\Desktop\Test Folder\" & filename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub



Thank you so much for any help. I really appreciate it.

 

[Collington]

Hi Philip,
that might require some debugging... I hereby commented the code:

Sub exportPages()

Set Sht = Worksheets("Sheet1")
' The variable Sht now holds the sheet that should be printed, so we can use that in the macro instead of repeating Worksheets("Sheet1")
ExportDir = "C:\temp\"
' A string/text with the export directory, should end with a \
NrPages = Sht.HPageBreaks.Count + 1
' Using the Sht object, count the number of horizontal page breaks and add one to know the number of pages

For p = 1 To NrPages
    ' Loop though the pages, actually the loop is only there as a kind of "counter"
    If p = 1 Then
        RwStart = 1
        ' The first page starts at the top and has no page break before it, so the row with your name is 1
    Else
        RwStart = Sht.HPageBreaks(p - 1).Location.Row
        ' Page 2 starts after Pagebreak 1, this code finds the row of the page break (=the row directly after it)
    End If
   
    FoundName = Sht.Range("B" & RwStart).Value
    ExportName = "Export_" & FoundName & "_" & p & ".pdf"
    ' Create a variable with the name in the sheet
   
    Sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ExportDir & ExportName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=p, to:=p, OpenAfterPublish:=False
    ' Export the current page with the current name to the default location
Next

Set Sht = Nothing
' Clean up variables

End Sub

Your end of the previous page question: just add -1 to "RwStart = Sht.HPageBreaks(p - 1).Location.Row" .

About your error 1004: at which line does that happen? And as a self-help course debugging:
No clue how good you are with VBA, but these are some general tips for working with VBA: for macro development, do the following; go to the VBA screen (ALT+F11) and
-in the VBA screen, do open the "view->Locals window" and the "view->immediate window"
-put break points in the code at places where you want it to stop (F9 or click in front of the line, line becomes dark red with a circle in front of it)
-if you then run the code (F5), the code will stop at that point and the locals window will give you insights into what the variables are/have in them
-use F8 to go step by step through your code and hover over variables to see what values they hold.
-use debug.print command to show the variables passing by

Ciao,
Koen

This worked perfectly for me! Thank you!!