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
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