Insert page breaks in Excel to split data for printing .pdf

Question

I am working with an Excel file that can contains dynamic rows. I export it to .pdf and it works well. The matter is that all the content is on a single page in the .pdf file.

Is there anyway to insert page breaks in my VBA for my .pdf file?

EDIT: Here is my code for the page break. Won't seems to get working

If count > 30 Then ' count is the number of row. Break at every 15 rows
        Set ws = Range("A1", "K" & count) 'The range of the document
        Dim ii As Integer
        ii = 21 ' first page break
        While count > 0
            If count > 15 Then ' no page break if there is less than 15 rows
             ws.Rows(ii).EntireRow.PageBreak = xlPageBreakManual
            End If               
            ii = ii + 15
            count = count - 15
        Wend
End If

EDIT 2: I did not find any way to create a page break into the pdf file. However, I still resolve my problem. I just find a module for vba to run external process and wait for them. it is ShellAndWait

I save every page of my pdf file as a temporary document and send it's path/name to a C# console application that I've created. I am using the library PDFsharp to process the pdf documents and then merge them to 1 file.

Hope it could help someone.

Solution

You can setup the page breaks like this :

Worksheets("Sheet1").HPageBreaks.Add Before:=Worksheets("Sheet1").Rows(25)
Worksheets("Sheet1").VPageBreaks.Add Before:=Worksheets("Sheet1").Columns("J")

Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual
Worksheets("Sheet1").Columns("J").PageBreak = xlPageBreakManual

FYI, I didn't managed to make the Range.PageBreak to work

Or for a range, something like this :

With Range(blabla)
    .Rows(.Rows.Count).EntireRow.PageBreak = xlPageBreakManual
    '.Rows(Int(.Rows.Count/2)).EntireRow.PageBreak = xlPageBreakManual
    .Columns(.Columns.Count).EntireColumn.PageBreak = xlPageBreakManual
End With

---

Here is your corrected code :

Sub test_Wanceslas()
Dim Ws As Worksheet, _
    Rg As Range, _
    LastRow As Long, _
    Count As Long, _
    ii As Long

ii = 21 ' first page break
Set Ws = ActiveSheet

With Ws
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    Count = LastRow
    Set Rg = .Range("A1", "K" & Count) 'The range of the document

    If LastRow > 30 Then ' count is the number of row. Break at every 15 rows
        .ResetAllPageBreaks
        .PageSetup.PrintArea = Rg.Address

        While Count > 0 And ii < LastRow
            If Count > 15 Then ' no page break if there is less than 15 rows left
                '.Rows(ii).PageBreak = xlPageBreakManual
                .HPageBreaks.Add Before:=.Rows(ii)
            End If
            ii = ii + 15
            Count = Count - 15
        Wend
    End If
End With

End Sub