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 11×17 paper. Somewhere, sometime, Excel decided that this is a “Custom page size”, which causes me problems. I need to reset all the pages to 11×17. Easy right? Select all the worksheets, go to Page Layout à Size and choose 11×17.

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:

  1. Sub Macro1()
  2. '
  3. ' Macro1 Macro
  4. '
  5. '
  6. Application.PrintCommunication = False
  7. With ActiveSheet.PageSetup
  8. .PrintTitleRows = "$1:$8"
  9. .PrintTitleColumns = ""
  10. End With
  11.  
  12. Application.PrintCommunication = True
  13. ActiveSheet.PageSetup.PrintArea = ""
  14. Application.PrintCommunication = False
  15. With ActiveSheet.PageSetup
  16. .LeftHeader = ""
  17. .CenterHeader = ""
  18. .RightHeader = "Printed &D &T"
  19. .LeftFooter = ""
  20. .CenterFooter = ""
  21. .RightFooter = ""
  22. .LeftMargin = Application.InchesToPoints(0.7)
  23. .RightMargin = Application.InchesToPoints(0.7)
  24. .TopMargin = Application.InchesToPoints(0.75)
  25. .BottomMargin = Application.InchesToPoints(0.75)
  26. .HeaderMargin = Application.InchesToPoints(0.3)
  27. .FooterMargin = Application.InchesToPoints(0.3)
  28. .PrintHeadings = False
  29. .PrintGridlines = False
  30. .PrintComments = xlPrintNoComments
  31. .PrintQuality = 600
  32. .CenterHorizontally = False
  33. .CenterVertically = False
  34. .Orientation = xlLandscape
  35. .Draft = False
  36. .PaperSize = xlPaper11x17
  37. .FirstPageNumber = xlAutomatic
  38. .Order = xlDownThenOver
  39. .BlackAndWhite = False
  40. .Zoom = False
  41. .FitToPagesWide = 1
  42. .FitToPagesTall = False
  43. .PrintErrors = xlPrintErrorsDisplayed
  44. .OddAndEvenPagesHeaderFooter = False
  45. .DifferentFirstPageHeaderFooter = False
  46. .ScaleWithDocHeaderFooter = True
  47. .AlignMarginsHeaderFooter = True
  48. .EvenPage.LeftHeader.Text = ""
  49. .EvenPage.CenterHeader.Text = ""
  50. .EvenPage.RightHeader.Text = ""
  51. .EvenPage.LeftFooter.Text = ""
  52. .EvenPage.CenterFooter.Text = ""
  53. .EvenPage.RightFooter.Text = ""
  54. .FirstPage.LeftHeader.Text = ""
  55. .FirstPage.CenterHeader.Text = ""
  56. .FirstPage.RightHeader.Text = ""
  57. .FirstPage.LeftFooter.Text = ""
  58. .FirstPage.CenterFooter.Text = ""
  59. .FirstPage.RightFooter.Text = ""
  60. End With
  61.  
  62. Application.PrintCommunication = True
  63.  
  64. End Sub

This is what I need:

  1. Sub Macro1()
  2. With ActiveSheet.PageSetup
  3. .PaperSize = xlPaper11x17
  4. End With
  5. End Sub

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

  1. Sub Macro1()
  2. Dim ws As Worksheet
  3.  
  4. For Each ws In ActiveWorkbook.Worksheets
  5. ws.PageSetup.PaperSize = xlPaper11x17
  6. Next ws
  7.  
  8. End Sub

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.

One thought on “Updating Page Sizes

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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>