Look what strange thing I discovered now:

I removed the fit to- statements (see other reply) but the problem remains.

Now, I've checked the menu InsertPage End (in Dutch InvoegenPagina Einde)
but for that row, the menu is InsertRemove Page End (in Dutch
InvoegenPagina-einde verwijderen), which means that the pagebreak is set
(thus the code is right).

So I can't understand why excel does not execute the break???

Thanks for your reply

Jos


Here's the code of the whole procedure


Function TBA_ListCreate()

Dim vRowIncrement As Integer
Dim vC_LLN As Integer, vHPB As Integer
Dim vKlas As String
Dim vLijst As String, vInput As String
Dim sLijst As Worksheet, sInput As Worksheet, sTemp As Worksheet

Dim wbFile As Workbook

DCL_Sheets

'Als de lijst vereist dat er een deliberatielijst moet gedraaid worden
gebeurt dit hier
'vinkje cbDeliberatielijst op formulier!

INIT_AKlas

vKlas = [gKlas]

vLijst = [gRapport] & [gExt]
Set sLijst = Sheets(vLijst)

vInput = sLijst.Cells(1011, 2)
Set sInput = wbData.Sheets(vInput)

Set vCell = sInput.Cells.Find(What:=vKlas, After:=sInput.Cells(3, 1),
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)
vRow_Input = vCell.Row

vStart = sLijst.Cells(1012, 2)
vStop = sLijst.Cells(1013, 2)
vRow = vStart
vRowIncrement = sLijst.Cells(1014, 2)

vC_LLN = 0
vHPB = 0

sLijst.Rows(vStart & ":" & vStop).Hidden = True

vLKLas = [gKlas]
vPswDsg = [gPswDsg]
vProtect = [gPProtect]

sLijst.PageSetup.PrintArea = ""
sLijst.ResetAllPageBreaks

sLijst.PageSetup.PrintArea = "$A$1:$" & GET_Col(sLijst.Cells(1016, 2)) & "$"
& sLijst.Cells(1013, 2)

'sLijst.PageSetup.FitToPagesWide = 1
'sLijst.PageSetup.FitToPagesTall = Int(40 / sLijst.Cells(1017, 2)) + 1

sLijst.PageSetup.PrintTitleRows = ""
If (sLijst.Cells(1018, 2) 0) Then
sLijst.PageSetup.PrintTitleRows = "$1:$" & sLijst.Cells(1018, 2)
End If

While (vKlas = [gKlas])

sLijst.Rows(vRow & ":" & vRow + vRowIncrement - 1).Hidden = False

'Leerlingenteller
vC_LLN = vC_LLN + 1

For i = 1 To 255

If (sLijst.Cells(1001, i) < "") Then

vCol = sLijst.Cells(1001, i)
sLijst.Cells(vRow, i) = sInput.Cells(vRow_Input, vCol)

vHide = sLijst.Cells(1002, i)

If (vHide < "") Then

If (sLijst.Cells(vRow, i) = vHide) Then
sLijst.Rows(vRow & ":" & vRow +
vRowIncrement).Hidden = True
vC_LLN = vC_LLN - 1

Exit For
End If

End If

End If

Next

vRow = vRow + vRowIncrement
vRow_Input = vRow_Input + 1
vKlas = sInput.Cells(vRow_Input, 1)

If (vC_LLN = sLijst.Cells(1017, 2)) Then
vC_LLN = 0

sLijst.HPageBreaks.Add Befo=sLijst.Cells(vRow, 1)

End If

Wend

sLijst.PrintOut Copies:=[gPCopies]

End Function

"Jos Vens" schreef in bericht
...
Hi,

I have a problem to establish horizontal pagebreaks for my worksheet.

The PrintArea is set from cell A1 to cell M300. I hide rows if they may
not
be seen.

this is the code I use:

sLijst.HPageBreaks.Add Befo=sLijst.Cells(vRow, 1)

where sLijst is the reference to the worksheet and vRow is the row
(integer
< 300) before which the pagebreak must be established. I make the breaks
in
a loop where vRow is added by 40 each time. No breaks are made.

Thanks for your help
Jos Vens