Updating Page Sizes

This drives me crazy. I'm not sure if it was the move to Excel 2013, or if it was something else…

I have this massive model, and we print it out on 11x17 paper. Somewhere, sometime, Excel decided that this is a "Custom page size", which causes me problems. I need to reset all the pages to 11x17. Easy right? Select all the worksheets, go to Page Layout à Size and choose 11x17.

Not so fast… if you do that, it replicates ALL the print settings including orientation, margins, fit to x pages by x pages, etc.. Nasty stuff. You can actually see why when you record a macro to change the paper size. This is what I get:

[code] Sub Macro1()
'
' Macro1 Macro
'
'
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$8"
.PrintTitleColumns = ""
End With

Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = "Printed &D &T"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaper11x17
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With

Application.PrintCommunication = True

End Sub[/code]

This is what I need:

[code]Sub Macro1()
With ActiveSheet.PageSetup
.PaperSize = xlPaper11x17
End With
End Sub[/code]

Actually, even more pointed, this will fix it without messing all my other settings up:

[code] Sub Macro1()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.PaperSize = xlPaper11x17
Next ws

End Sub[/code]

I know why the code records as it does, as Excel doesn't know what settings I truly need, so it records the current state of all PageSetup stuff. But I sure wish when I tried to update one print setting via the user interface that it gave me the ONE I changed, not everything.

2 thoughts on “Updating Page Sizes

  1. Agree, Toggling between Record All Settings and Record Changed Settings should be an option button in the Macro Recorder

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *