[SOLVED] Insert page break every nth row

Insert page break every nth row

I have been trying to insert page breaks every n rows in LibreOffice Calc. I have tried a macro which doesn't seem to work:

Sub PAGE_BREAK() 
  For MY_ROWS = 50 To Range("A65536").End(xlUp).Row Step 50
     ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A" & MY_ROWS)
  Next MY_ROWS
End Sub

I get a sub-procedure or function not defined error for the above code.

Any ideas how I can do this more easily?

Solution

Inserting page breaks in LibreOffice Calc sheets works a little bit different than in MS Office VBA. With LibreOffice Basic (or OpenOffice.org Basic), you insert a (horizontal) page break by setting the row's IsStartOfNewPage property to true. To access every nth row, you could simply use ThisComponent.Sheets(0).Row(n).

So, combining this in a LibreOffice Basic macro, the result could look like this:

sub pagebreak
rem ----------------------------------------------------------------------
rem define variables
Dim Sheet As Object
Dim Cell As Object   

Sheet = ThisComponent.Sheets(0)

for i = 10 to 100 Step 10
    Sheet.Rows(i).IsStartOfNewPage = true
next i

end sub

This macro just inserts a manual page break after each 10th row (notice that the first row is Rows(0), so Rows(10) is row # 11), up to row 101 of the current sheet. You can easily adjust that code to fit your needs (max. number of rows etc).