How to set 'Fit all columns on one page' in print tab

vba excel excel-2013
Pepys picture Pepys · Sep 9, 2014 · Viewed 60.6k times · Source

The only thing I would want is to always set the 'Fit all columns on one page' setting when users open the Print tab in Excel.

And no, they don't want to do it themselves. What a surprise :)

Here is a screenshot of where that is in Excel 2013:enter image description here

Tried to look for some VBA code like the following but without success.

With Sheets("Print Letter").PageSetup .FitToPagesWide = 1 .FitToPagesTall = 1 End With

Answer

user2140173 picture user2140173 · Sep 9, 2014

Try setting the .FitToPagesTall to False to be able to manually set the .FitToPagesWide property.

MSDN link

If this property is False, Microsoft Excel scales the worksheet according to the FitToPagesWide property. If the Zoom property is True, the FitToPagesTall property is ignored.

Sub PrintColumns()
    Application.PrintCommunication = False
    With Sheets("Print Letter").PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.PrintCommunication = True
End Sub

Related questions

Clear contents and formatting of an Excel cell with a single command

If you want to clear the contents of a cell or range in Microsoft Excel, you can use .ClearContents. If you also want to clear the formatting, you can use .ClearFormats. Sheets("Test").Range("A1:C3").ClearContents Sheets("Test").Range("…

Read More
Remove Duplicates from range of cells in excel vba

I'm trying to remove duplicates in excel 2013 VBA. but I'm getting error "object does not support this property or method". The problem is I don't have static range to select. I want remove duplicates from the column heaader 'abcd'. Cells.…

Read More
No Add-In Tab Though Add-In is Loaded and Working

I wrote an Excel VBA add-in over ten years ago, which uses the old Commandbars to put a menu in Excel's Tools menu (in Excel 2003 and earlier) or in Excel's Add-Ins tab (in Excel 2007 and later). I've never had a …

Read More