Variable Page Break
 

Hello,

I have a table that has the date in column A. The start date is variable based on the value in O1. From there, the next cell is calculate from the one above. This goes on to A368.

A | ... | O |
1 DATE 10/1/2016
2 =O1
3 =A2+1
4 =A3+1
5 ...



A | ... | O |
1 DATE 10/1/2016
2 2016/10/01
3 2016/10/02
4 2016/10/03
5 ...

So, my question is: Is there a way to reset the page breaks auto-magickally so that it prints one month range per page, columns A through K? Also, to only print 12 months worth.

Thank you,
~~Chris

Variable Page Break
 

Hi Chris,

Am Tue, 6 Oct 2015 17:26:10 -0700 (PDT) schrieb Christopher Jack:

I have a table that has the date in column A. The start date is variable based on the value in O1. From there, the next cell is calculate from the one above. This goes on to A368.

A | ... | O |
1 DATE 10/1/2016
2 =O1
3 =A2+1
4 =A3+1
5 ...

So, my question is: Is there a way to reset the page breaks auto-magickally so that it prints one month range per page, columns A through K? Also, to only print 12 months worth.

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

Variable Page Break
 

Just a hint to a better way to enter incremental values into contiguous
cells without having to type cell addresses...

A: Use a local scope (sheet level) fully relative defined name to ref
the cell above the active cell.
A1. Select A2 and open the DefineName dialog;
A2. Type in the namebox...
'<sheetname'!LastCell
..where <sheetname is the actual sheetname;
A3. Tab into the RefersTo box and type...
=A1
..making sure the above is the only contents in the box;
A4. Click Add, then close the dialog.

B: Put a common formula in all cells below the start value to be
incremented with the increment value.
B1. Enter a date in A2;
B2. Select A3:A368;
B3. Just start typing...
=LastCell+1
..into the active cell;
B4. Hold down Ctrl and press Enter to place the formula
in all selected cells in one shot

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

Variable Page Break
 

On Wednesday, October 7, 2015 at 4:15:47 AM UTC-5, GS wrote:
Just a hint to a better way to enter incremental values into contiguous
cells without having to type cell addresses...

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thank you for this tip. I didn't even know that was there, nor how to use it.

~~Chris

Variable Page Break
 

Hi Chris,

Am Thu, 8 Oct 2015 17:42:35 -0700 (PDT) schrieb Christopher Jack:

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.

you don't need another IF. Change
If Month(rngC) Month(rngC.Offset(-1, 0)) Then
to
If Month(rngC) < Month(rngC.Offset(-1, 0)) Then:

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 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