Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rowTblStart As Long
Dim rowTblSubtotaal As Long
Dim rowNextPageBreak As Long
Dim rowMax As Long
Dim iPageBreak As Long
Dim rowSelect As Long
If Target.Count <> 1 Then Exit Sub
If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target.Offset(-1).Interior.Color = rgbYellow Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Target.EntireRow
.Copy
.Insert Shift:=xlDown
.Offset(-1).Resize(, 2).ClearContents
End With
rowSelect = Target.Row
Set ws = ActiveWorkbook.Sheets("STELPOSTEN")
ActiveWindow.View = xlPageBreakPreview
' Alle pagebreaks verwijderen
ActiveSheet.ResetAllPageBreaks
' Bepalen van de variabelen
rowMax = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row + 2
rowTblSubtotaal = 0
If ws.HPageBreaks.Count > 0 Then
iPageBreak = 1
Else
iPageBreak = 0
End If
While rowTblSubtotaal + 2 < rowMax
rowTblSubtotaal = Cells.Find(What:="SUBTOTAAL", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Row + 1
With ws.HPageBreaks
If iPageBreak <= .Count Then
rowNextPageBreak = .Item(iPageBreak).Location.Row
Cells(rowTblSubtotaal, "F").Activate
If ActiveCell.Row > ws.HPageBreaks.Item(iPageBreak).Location.Row Then
rowTblStart = Cells.Find(What:="KOSTPRIJS", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True, SearchFormat:=False).Row
ws.HPageBreaks.Add before:=Cells(rowTblStart - 1, "F")
iPageBreak = iPageBreak + 1
End If
End If
End With
Wend
ActiveWindow.View = xlNormalView
Cells(rowSelect, "C").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub