Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 Page Break Adjustments causes a page break each cell BKaufman Excel Worksheet Functions 2 September 10th 10 05:02 AM
How do I do page breaks when view menu doesnt page break preview HeatherF55 Excel Discussion (Misc queries) 0 September 21st 07 04:24 AM
change page number watermark in page break preview juga Excel Discussion (Misc queries) 2 December 25th 06 11:16 AM
change and/or remove page number watermark in page break preview juga Excel Discussion (Misc queries) 2 December 25th 06 11:15 AM
adding a new page break to an existing page break Edward Letendre Excel Discussion (Misc queries) 1 March 6th 05 10:29 AM


All times are GMT +1. The time now is 04:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"