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.

 

[Rijnsent]

Hi DPhilip,

Some code that I recorded:

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\temp\Book1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True _
        , IgnorePrintAreas:=False, From:=2, To:=2, OpenAfterPublish:=True

After "Save as", select PDF and then change the page range (with the Option button that appears). Basically the "From" and "To" element determine the pages from the sheet that are printed. In a loop it would be something like (not tested):

Sub exportPages()

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

For p = 1 To NrPages
    ExportName = "ExportFile_" & 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

Cheers,

Koen

 

[DPhilip]

Works great! Can you point to me how I can name the file based on value in the B Cell under each page break?

 

[Rijnsent]

Hi Philip,
try something like this:

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

Cheers,
Koen

 

[DPhilip]

Koen, you are a life saver. Just so I can understand, if I wanted to add the cell above the page break, what exactly do I edit?

 

[DPhilip]

Also how do I stop the run time error 1004? It seems its not finding an end loop..

 

[Rijnsent]

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

 

[DPhilip]

Thank you so much!

 

[Mia66]

Hi, thank you for this as this is working for me!
I have another small request, I only want to print where there is data in the cells and not all of the page breaks. I have an excel that I prepared with page breaks and sometimes I only have 5 lines and sometimes I have 20 lines. If i do a new VBA to select the lines that I need, I have:

Range("A1:E12").Select
Range(Selection, Selection.End(xlDown)).Select
I need to print only the selection from above to separate PDF.
Can you give me a VBA that only select the lines that contain data?
Thanks in advance!

 

[shuvosaha]

Hi Rijnsent,

Thank you so much for the code. However I have a small addition to this code would help me a lot. Could you please tell me how can I have the separate pages by cell value or from a blank to before another blank row.
I actually I a single column which contains thousands of rows, and from there I need to export pdf from single page to multiple pages. This page break pdf export helps to export single page for each page break but if I need multiple as well how would I achieve this?

Thanks
Shuvo