Add page break after every 48th rows + border after last row on page

Hello all,

Since 3 hours I am trying to do a small macro that will add a page break after every 48th row and a border after the last row in page, but it seems that it just doesn't work...

I also tried to add the page break manually (macro recorder)

ActiveSheet.ResetAllPageBreaks
Set ActiveSheet.HPageBreaks(1).location = Range("A48")
Set ActiveSheet.HPageBreaks(2).location = Range("A96")
Set ActiveSheet.HPageBreaks(3).location = Range("A144")

but since my number of pages are not always the same, it gives me an error when the range with data is shorter then the row number on vba...

Any chance you can help?

hi,

this does the trick

Sub SetPageBreaks() 'set horizontale pagebreaks
   Dim PrintArea As String, LocArr As Variant, nmbPages As Long, I As Long, RowsPerPage As Long
   
   RowsPerPage = 48 ' change to whatever you need
   
   PrintArea = ActiveSheet.PageSetup.PrintArea
   PrintArea = Replace(PrintArea, ":", "", , , vbBinaryCompare)
   PrintArea = Mid(PrintArea, 2, Len(PrintArea) - 1)
   LocArr = Split(PrintArea, "$", , vbBinaryCompare)

   nmbPages = Application.WorksheetFunction.RoundUp((CDec(LocArr(3)) - CDec(LocArr(1))) / RowsPerPage, 0)
   
   ActiveSheet.ResetAllPageBreaks
   For I = 1 To nmbPages - 1
      ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveSheet.Range("A" & (CDec(LocArr(1)) + (RowsPerPage * I)))
   Next I
End Sub

Select you print area and run the code

Can you debug code?
go to VBE and set your cursor inside the Sub SetPagebreak()
now press F8
The yellow row will apear now press F8 again to go trough the code untill the error

What is the contents of the variable PrintArea? you can find it in local variables section or move your mouse cursor over the variable word