Variable Page Break
On Wednesday, October 7, 2015 at 2:42:25 AM UTC-5, Claus Busch wrote:
Hi Chris,
try it with VBA. Right click on sheet tab = View Code and paste the
code into the code window. If you change O1 the pagebreaks will be
changed:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "O1" Then Exit Sub
Dim rngC As Range
With ActiveSheet
With .PageSetup
.PrintArea = "A1:K368"
.Orientation = xlLandscape
End With
.ResetAllPageBreaks
For Each rngC In .Range("A3:A368")
If Month(rngC) Month(rngC.Offset(-1, 0)) Then
.HPageBreaks.Add rngC
End If
Next
End With
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Thank you for your help. This works until it gets to the next year. Then January is less then December, and doesn't work until February. The page break ends up being a few rows down and dotted when viewed in page break view.
I also changed it so that it prints Portrait instead of Landscape.
P.S.
I think I figured it out. I added a second For...Next to check for Year. It works, but if there is a more appropriate way, please share.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "O1" Then Exit Sub
Dim rngC As Range
With ActiveSheet
With .PageSetup
.PrintArea = "A1:K368"
.Orientation = xlPortrait
End With
.ResetAllPageBreaks
For Each rngC In .Range("A3:A368")
If Year(rngC) Year(rngC.Offset(-1, 0)) Then
.HPageBreaks.Add rngC
End If
Next
For Each rngC In .Range("A3:A368")
If Month(rngC) Month(rngC.Offset(-1, 0)) Then
.HPageBreaks.Add rngC
End If
Next
End With
End Sub
Thanx
~~Chris
|