Report: Copying pagebreaks of a reference sheet to other sheets

[masayoshi hayashi]

Here is my report I'd like to share.

I found many helpful info on vpagebreaks and hpagebreaks objects in
the group. I created a code to copy pagebreaks of a reference sheet to
other sheets in the same workbook like other people attempted.

Here is my experience:
pageset.zoom = false does not seem to work for eliminating automatic
pagebreaks once they are present in "other sheets". .zoom = false
seems to work before when pressing the print preview button in a sheet
for the first time.

To properly set pagebreaks of "other sheets" from the reference sheet,
one way is to set
..FitToPagesTall = RbMax + 1
..FitToPagesWide = CbMax + 1

in the code below, where RbMax + 1 is the number of hpagebreaks and
CbMax + 1 that of vpagebreaks in the reference sheet. I also tried
adding and removing .zoom=false but it did not make any difference.

My code seems to work in the mixture of automatic and manual
pagebreaks in both reference and other sheets.

Using my codes, excel does not show preview pagebreak blue lines in
the sheet, but the pages are properly broken when viewed in the
preview window. For example, I add hpagebreak before row 10 but no
blue line is there in the sheet in the preview mode.

The presence of Either/Both lines
..Cells.PageBreak = xlPageBreakNone
..ResetAllPageBreaks
did not make any difference in the result so I commented out. I guess
explicit statements of .fittopagestall and .fittopageswide take over
all kinds of automatic/manual pagebreak settings.

Maybe I am missing something but I'd be appreciated with any comments
for additional insights.

---------------------------------------------------
Using the function sets posted by Myrna Larson in
http://groups.google.com/[email protected]&rnum=7

Function PageBreakRows() As Variant
Dim V As Variant, n As Long, i As Long

Application.ScreenUpdating = False
ActiveWindow.View = xlPageBreakPreview
With ActiveSheet
n = .HPageBreaks.Count
ReDim V(0 To n)
V(0) = 1
For i = 1 To n
V(i) = .HPageBreaks(i).Location.Row
Next i
End With
ActiveWindow.View = xlNormalView
Application.ScreenUpdating = True
PageBreakRows = V
End Function

Function PageBreakColumns() As Variant
Dim V As Variant, n As Long, i As Long

Application.ScreenUpdating = False
ActiveWindow.View = xlPageBreakPreview
With ActiveSheet
n = .VPageBreaks.Count
ReDim V(0 To n)
V(0) = 1
For i = 1 To n
V(i) = .VPageBreaks(i).Location.Column
Next i
End With
ActiveWindow.View = xlNormalView
Application.ScreenUpdating = True
PageBreakColumns = V
End Function

Private Sub OKButton_Click()
Dim Ref As PageSetup
Dim SelSh As String
Dim Sh As Variant
Dim SCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Rb As Variant
Dim Cb As Variant
Dim RbMax As Integer
Dim CbMax As Integer

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set Ref = Sheets(ListBox1.Value).PageSetup

SCount = LBSelectedSheets.ListCount

If SCount = 0 Then
MsgBox "No sheet is Selected."
Exit Sub
Else
Rb = PageBreakRows
Cb = PageBreakColumns
RbMax = UBound(Rb)
CbMax = UBound(Cb)

For i = 0 To SCount - 1
SelSh = LBSelectedSheets.List(i)

With Sheets(SelSh)
With .PageSetup
.Zoom = False
.PrintArea = Ref.PrintArea
.LeftHeader = Ref.LeftFooter
.CenterHeader = SelSh & " &P/&N"
.RightHeader = Ref.RightHeader
.LeftFooter = Ref.LeftFooter
.CenterFooter = Ref.CenterFooter
.RightFooter = Ref.RightFooter
.LeftMargin = Ref.LeftMargin
.RightMargin = Ref.RightMargin
.TopMargin = Ref.TopMargin
.BottomMargin = Ref.BottomMargin
.HeaderMargin = Ref.HeaderMargin
.FooterMargin = Ref.FooterMargin
.PrintHeadings = Ref.PrintHeadings
.PrintGridlines = Ref.PrintGridlines
.PrintComments = Ref.PrintComments
.CenterHorizontally = Ref.CenterHorizontally
.CenterVertically = Ref.CenterVertically
.Orientation = Ref.Orientation
.Draft = Ref.Draft
.PaperSize = Ref.PaperSize
.FirstPageNumber = Ref.FirstPageNumber
.Order = Ref.Order
.BlackAndWhite = Ref.BlackAndWhite
.PrintErrors = Ref.PrintErrors
.FitToPagesTall = RbMax + 1
.FitToPagesWide = CbMax + 1
' .Zoom = False
End With


' The code below only removes manually set pagebreaks.
' .Cells.PageBreak = xlPageBreakNone
' .ResetAllPageBreaks

For j = 1 To RbMax ' j starts from 1 because
PageBreakRows function assigns 1 to Rb(0).
.HPageBreaks.Add before:=.Cells(Rb(j), 1)
Next j

For k = 1 To CbMax
.VPageBreaks.Add before:=.Cells(1, Cb(k))
Next k
End With
Next i
End If

Application.Calculation = xlCalculationAutomatic

Unload Me
End Sub

 

[masayoshi hayashi]

Correction: .zoom = false makes a difference. I didn't notice I'd put
..zoom = false statement twice.

Also .LeftHeader = Ref.LeftHeader and .CenterHeader = Ref.CenterHeader
(I noticed I could use &A in the reference sheet instead so
..centerheader = ref.centerheader is much better.... )

With Sheets(SelSh)
With .PageSetup
.Zoom = False
.PrintArea = Ref.PrintArea
.LeftHeader = Ref.LeftHeader
.CenterHeader = Ref.CenterHeader

and please exclude the last line .zoom = False before End with of
..pagesetup.

BTW, setting
.FitToPagesTall = ref.FitToPagesTall
.FitToPagesWide = ref.FitToPagesWide

does not result in correct pagebreaks in "other sheets".
Even if I set say, 64% in zoom option in the reference sheet hence not
selecting fittopages options, excel assigns 1 to both
ref.FitToPagesTall and ref.FitToPagesWide.