Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks in VBA Q
I have the macro below to print a range A:HB to 29 pages, problem is however that the Page breaks are not consistent, how can I alter the page breaks within the macro?
Sub Print_Per() Application.ScreenUpdating = False Sheets("Month").Select ActiveSheet.PageSetup.PrintArea = "$A$66:$HB$99" With ActiveSheet.PageSetup .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .FitToPagesWide = 29 .FitToPagesTall = 1 End With ActiveWindow.SelectedSheets.PrintOut Copies:=1 Range("A1").Select Sheets("Header").Select Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks in VBA Q
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks in VBA Q
Macro as per below, only prints out whats in the sheet "Header", whats in 'Month" and specified range is what I'm after
Sub Print_Per() Dim i As Integer Application.ScreenUpdating = False With Sheets("Month") With .PageSetup .PrintArea = "$A$66:$HB$99" .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver End With .ResetAllPageBreaks .HPageBreaks.Add .Range("I100") For i = 1 To 29 .VPageBreaks.Add .Cells(1, i * 7) Next End With ActiveWindow.SelectedSheets.PrintOut Copies:=1 Range("A1").Select Application.Goto Sheets("Header").Range("A1") End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks in VBA Q
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks in VBA Q
Thanks, that worked, but pretty much prints out what I had before.
I want 7 Columns per page (203 columns i.e. A:GU; with 7 per page = 29 pages) , but only 24 pages print out, thus it would appear 1-8 columns print on 1st page, 9-18 columns on 2nd etc I don't have any hidden columns and each of the batches of 7 columns are similarly sized |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks in VBA Q
Hi Sean,
Am Mon, 23 Jul 2012 07:44:38 -0700 (PDT) schrieb : Thanks, that worked, but pretty much prints out what I had before. I want 7 Columns per page (203 columns i.e. A:GU; with 7 per page = 29 pages) , but only 24 pages print out, thus it would appear 1-8 columns print on 1st page, 9-18 columns on 2nd etc then change the code: For i = 1 To 29 .VPageBreaks.Add .Cells(1, i * 8) Next with i*8 you have 7 columns per page Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks in VBA Q
That didn't seem to make any difference, still prints to 24 pages.
A:H prints Page 1 (although could be to Col I, as I don't have grid lines shown and Col I is blank) I:R prints Page 2 (which is 10 columns) Code as below Sub Print_Per() Dim i As Integer Application.ScreenUpdating = False With Sheets("Month") With .PageSetup .PrintArea = "$A$66:$GU$99" .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver End With .ResetAllPageBreaks .HPageBreaks.Add .Range("I100") For i = 1 To 29 .VPageBreaks.Add .Cells(1, i * 8) Next .PrintOut Copies:=1 End With Application.Goto Sheets("Header").Range("A1") End sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I can't move my page breaks in Page Break Preview | Excel Discussion (Misc queries) | |||
Page Preview showing placement of page breaks | About this forum | |||
Vertical page breaks won't drag in Page Break Preview | Excel Discussion (Misc queries) | |||
How do I do page breaks when view menu doesnt page break preview | Excel Discussion (Misc queries) | |||
Page Breaks- Printing selected rows on same page | Excel Discussion (Misc queries) |