Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Page Break Adjustments causes a page break each cell | Excel Worksheet Functions | |||
How do I do page breaks when view menu doesnt page break preview | Excel Discussion (Misc queries) | |||
change page number watermark in page break preview | Excel Discussion (Misc queries) | |||
change and/or remove page number watermark in page break preview | Excel Discussion (Misc queries) | |||
adding a new page break to an existing page break | Excel Discussion (Misc queries) |