Updating Page Sizes

Posted on January 30th, 2013 in Excel,I hate it when... by Ken Puls

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:

code
< view plain text >
  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:

code
< view plain text >
  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:

code
< view plain text >
  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.

1 Comment

One Response to 'Updating Page Sizes'

Subscribe to comments with RSS or TrackBack to 'Updating Page Sizes'.

  1. Hui... said,

    on January 30th, 2013 at 5:15 pm

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

Post a comment