Thread: Universal Macro to Fit All Columns to Page

Hi All,

Thank you for the help.

So, I wrote a macro, which I put in the MSOffice Library so that I can embed the macro on the Excel Ribbon. I would like to incorporate the Fit All Columns to Page. However, it doesn't seem to work if I set the macro up for universal use.

Error message: Application-defined or object-defined error

If I change sheet1 to activesheet, the error will be: Object variable or With block variable not set

Any advice?

Application.PrintCommunication = False
With Sheet1.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.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 = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 0
.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

Welcome to Excel Forum!

I could not find a reference to Application.PrintCommunication in Excel 2007. When I use it I get the same error you are describing. An online search shows it in Excel 2010 but your profile says 2003. Did you copy that code from someplace intended for Excel 2010? Not sure why you want to do that anyway. It is not necessary to turn off printer communication to change page setup.

I have several other pieces of advice:

First, forum rules require that code be enclosed in code tags. See my signature below.

Second, when debugging macros it helps to know exactly what line triggers the error. When the error occurs, do you see what line it's stopping on? If not you may want to run it in the VBA environment so you can step through it with the debugger.

Third, it looks like your code was generated by the macro recorder. It certainly works, but the macro recorder will generate a line of code for every possible attribute, not just the ones you actually set. What you describe that you need can be done by this Subm which worked for me:

Sub FitToPageWidth()
   With ActiveSheet.PageSetup
      .Zoom = False
      .FitToPagesWide = 1
      .FitToPagesTall = False
   End With
End Sub

instead of everything between the With and End With.