Copy print settings form sheet to sheet in VBA

[Jo4x4]

Hi everybody,
WinXP, Excel 2007

I have a button on one sheet, that copies current information to another sheet in the same workbook.
The problem is that the print settings are not copied. Specifically the headers and footers are not copied, but also the row spacing is not copied.

Here is the code (in a module):

Sub Copy_Print_Settings(OldSht, NewSht)

Dim MyWrkBook As String

Worksheets(NewSht).Select

With ActiveSheet.PageSetup
.PrintArea = Worksheets(OldSht).PageSetup.PrintArea
.LeftHeader = Worksheets(OldSht).PageSetup.LeftHeader
.CenterHeader = Worksheets(OldSht).PageSetup.CenterHeader
.RightHeader = Worksheets(OldSht).PageSetup.RightHeader
.LeftFooter = Worksheets(OldSht).PageSetup.LeftFooter
.CenterFooter = Worksheets(OldSht).PageSetup.CenterFooter
.RightFooter = Worksheets(OldSht).PageSetup.RightFooter
.LeftMargin = Worksheets(OldSht).PageSetup.LeftMargin
.RightMargin = Worksheets(OldSht).PageSetup.RightMargin
.TopMargin = Worksheets(OldSht).PageSetup.TopMargin
.BottomMargin = Worksheets(OldSht).PageSetup.BottomMargin
.HeaderMargin = Worksheets(OldSht).PageSetup.HeaderMargin
.FooterMargin = Worksheets(OldSht).PageSetup.FooterMargin
.PrintHeadings = Worksheets(OldSht).PageSetup.PrintHeadings
.PrintGridlines = Worksheets(OldSht).PageSetup.PrintGridlines
.PrintComments = Worksheets(OldSht).PageSetup.PrintComments
.CenterHorizontally = Worksheets(OldSht).PageSetup.CenterHorizontally
.CenterVertically = Worksheets(OldSht).PageSetup.CenterVertically
.Orientation = Worksheets(OldSht).PageSetup.Orientation
.Draft = Worksheets(OldSht).PageSetup.Draft
.PaperSize = Worksheets(OldSht).PageSetup.PaperSize
.FirstPageNumber = Worksheets(OldSht).PageSetup.FirstPageNumber
.Order = Worksheets(OldSht).PageSetup.Order
.BlackAndWhite = Worksheets(OldSht).PageSetup.BlackAndWhite
.Zoom = Worksheets(OldSht).PageSetup.Zoom
.FitToPagesWide = Worksheets(OldSht).PageSetup.FitToPagesWide
.FitToPagesTall = Worksheets(OldSht).PageSetup.FitToPagesTall
.PrintErrors = Worksheets(OldSht).PageSetup.PrintErrors

.OddAndEvenPagesHeaderFooter = Worksheets(OldSht).PageSetup.OddAndEvenPagesHeaderFooter
.DifferentFirstPageHeaderFooter = Worksheets(OldSht).PageSetup.DifferentFirstPageHeaderFooter
.ScaleWithDocHeaderFooter = Worksheets(OldSht).PageSetup.ScaleWithDocHeaderFooter
.AlignMarginsHeaderFooter = Worksheets(OldSht).PageSetup.AlignMarginsHeaderFooter

.EvenPage.LeftHeader.Text = Worksheets(OldSht).PageSetup.EvenPage.LeftHeader.Text
.EvenPage.CenterHeader.Text = Worksheets(OldSht).PageSetup.EvenPage.CenterHeader.Text
.EvenPage.RightHeader.Text = Worksheets(OldSht).PageSetup.EvenPage.RightHeader.Text
.EvenPage.LeftFooter.Text = Worksheets(OldSht).PageSetup.EvenPage.LeftFooter.Text
.EvenPage.CenterFooter.Text = Worksheets(OldSht).PageSetup.EvenPage.CenterFooter.Text
.EvenPage.RightFooter.Text = Worksheets(OldSht).PageSetup.EvenPage.RightFooter.Text

.FirstPage.LeftHeader.Text = Worksheets(OldSht).PageSetup.FirstPage.LeftHeader.Text
.FirstPage.CenterHeader.Text = Worksheets(OldSht).PageSetup.FirstPage.CenterHeader.Text
.FirstPage.RightHeader.Text = Worksheets(OldSht).PageSetup.FirstPage.RightHeader.Text
.FirstPage.LeftFooter.Text = Worksheets(OldSht).PageSetup.FirstPage.LeftFooter.Text
.FirstPage.CenterFooter.Text = Worksheets(OldSht).PageSetup.FirstPage.CenterFooter.Text
.FirstPage.RightFooter.Text = Worksheets(OldSht).PageSetup.FirstPage.RightFooter.Text

End With
End Sub


Thanks in advance
Jo

 

[doofusboy]

You could try something like this:

Dim ws As Worksheet
 
    For Each ws In Worksheets
        With ws.PageSetup
        '.PrintArea = whatever setting should be
        '.LeftHeader = whatever setting should be
        '.CenterHeader = whatever setting should be
        '.RightHeader = whatever setting should be
        End With
    Next ws

Obviously you would do this for all lines of your print setting code.

 

[Jo4x4]

Hi, Please understand that I have no knowledge of VBA. Where should this code of yours go?

Thanks
Jo