Calc PageBreak at set points

[chris186h]

Hello everyone. So i have a list of names in calc. I have been trying to inset a macro but am struggling.
I want to inset a page break into that list so that names beginning with A-D are on one sheet. E-H on another, I-L on another so on and so on.

[JohnSUN]

Do you mean something like as

Sub breakByLetters(Optional letterCount As Integer, Optional colOfNames As Integer)
Dim oCurrentController As Variant
Dim oActiveSheet As Variant
Dim oCursor As Variant
Dim oDataArray As Variant
Dim oRows As Variant
Dim oRow As Variant
Dim i As Long
Dim baseLetter As Integer
Dim nextLetter As Integer
Dim currLetter As Integer
	If isMissing(letterCount) Then letterCount=4	' A-D;E-H;I-L...'
	If isMissing(colOfNames) Then colOfNames=0		' By default Names in column A'

	oCurrentController = ThisComponent.getCurrentController()
	oActiveSheet = oCurrentController.getActiveSheet()
	oActiveSheet.removeAllManualPageBreaks()	' Remove old Manual PageBreaks'

	oCursor = oActiveSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oDataArray = oCursor.getDataArray()			' All data in current sheet'
	oRows = oCursor.getRows()

' If first row is column heading. Otherwise - oDataArray(0) and For i=1...'
	baseLetter = Asc("A")
	currLetter = Asc(UCase(Trim(oDataArray(1)(colOfNames))))
	nextLetter = baseLetter + Int((currLetter-baseLetter) / letterCount + 1) * letterCount
	For i = 2 To UBound(oDataArray)
		currLetter = Asc(UCase(Trim(oDataArray(i)(colOfNames))))
		If currLetter>=nextLetter Then
			oRow = oRows.getByIndex(i)
			oRow.IsManualPageBreak = True
			oRow.IsStartOfNewPage = True
			nextLetter = baseLetter + Int((currLetter-baseLetter) / letterCount + 1) * letterCount
		EndIf
	Next i
End Sub

Another version - with counting rows on each page (after your remark about “randomly breaking”)

Sub breakByLetters(Optional letterCount As Integer, Optional colOfNames As Integer, Optional minRowsOnPage As Integer)
Dim oActiveSheet As Variant
Dim oCursor As Variant
Dim oDataArray As Variant
Dim oRows As Variant
Dim oRow As Variant
Dim i As Long
Dim baseLetter As Integer
Dim nextLetter As Integer
Dim currLetter As Integer
Dim lenOfCurrPage As Integer
' old revision	If isMissing(letterCount) Then letterCount=4	' A-D;E-H;I-L...
	If isMissing(letterCount) Then letterCount=1	' A;B;C;D;E...'
	If isMissing(colOfNames) Then colOfNames=0		' By default Names in column A'
	If isMissing(minRowsOnPage) Then minRowsOnPage=10	' If page shortly - not break it'

	oActiveSheet = ThisComponent.getCurrentController().getActiveSheet()
	oActiveSheet.removeAllManualPageBreaks()	' Remove old Manual PageBreaks

	oCursor = oActiveSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oDataArray = oCursor.getDataArray()			' All data in current sheet
	oRows = oCursor.getRows()

' If first row is column heading. Otherwise - oDataArray(0) and For i=1...'
	baseLetter = Asc("A")
	currLetter = Asc(UCase(Trim(oDataArray(1)(colOfNames))))
	nextLetter = baseLetter + Int((currLetter-baseLetter) / letterCount + 1) * letterCount
	lenOfCurrPage = 1
	For i = 2 To UBound(oDataArray)
		lenOfCurrPage = lenOfCurrPage + 1
		currLetter = Asc(UCase(Trim(oDataArray(i)(colOfNames))))
		If currLetter>=nextLetter Then
			nextLetter = baseLetter + Int((currLetter-baseLetter) / letterCount + 1) * letterCount
			If lenOfCurrPage>=minRowsOnPage Then
				oRow = oRows.getByIndex(i)
				oRow.IsManualPageBreak = True
				oRow.IsStartOfNewPage = True
				lenOfCurrPage = 1
			EndIf
		EndIf
	Next i
End Sub

[chris186h]

That worked perfectly. Thankyou very much. Is there a way of defining the name range rather than relying on lettercount
So in the future if i want to change the split. A-F, G-I, J-P
Making it more random then using a count
Thankyou once again

[chris186h]

Thankyou very much for your help.