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.