CODEWITHSUNDEEP
excelvbaprintingpage-setup
graham.reeds
graham.reeds

Reputation: 16476

PageSetup problems

In what is hopefully the last problem in the "Print An Individual Range" series of questions, I hereby humbly ask the more knowledgable readers how to copy the PageSetup object from one page to another.

Since you can simply Dest.PageSetup = Source.PageSetup I have had to create function that does the same. An abridge form is below:

Public Sub CopyPageSetup(ByVal Source As Worksheet, ByRef Dest As Worksheet)
    With Source.PageSetup
        Call SetParam(.AlignMarginsHeaderFooter, Dest.PageSetup.AlignMarginsHeaderFooter)
        ' etc
        Call SetParam(.Zoom, Dest.PageSetup.Zoom)
    End With
End Sub

And SetParam is simply:

Public Sub SetParam(ByVal Source As Variant, ByRef Dest As Variant)
    If Dest <> Source Then Dest = Source
End Sub

However this does not copy the page setup across - immeadiately after calling this function I call tmp.PrintPreview (where tmp is a temporary worksheet) and the output is the same as if I had never called the function.

Is this a mirage (does it work for you?) and if it is not a mirage what do I need to do to correct this?

Upvotes: 0

Views: 467

Answers (1)

Stewbob
Stewbob

Reputation: 16899

My version of Excel (2003) doesn't support the .AlignMarginsHeaderFooter property, but I got the zoom and BottomMargin properties to copy to the new page just fine.

Public Sub CopyPageSetup(ByVal Source As Worksheet, ByRef Dest As Worksheet)
   With Dest.PageSetup
        .Zoom = Source.PageSetup.Zoom
        .BottomMargin = Source.PageSetup.BottomMargin
   End With
End Sub

Upvotes: 2

Related Questions