[Excel VBA] Complete Print Settings with One Click! With Code Explanation

2025年12月30日

When printing a sheet created in Excel, do you often want to “fit it to one page,” “set it to portrait,” or “set it to A4 size”? Manually configuring these settings every time is a hassle, but with VBA, you can complete it with a single click. In this article, I will introduce a simple VBA code specialized for print settings.

VBA Code for Print Settings

Below is the VBA code to configure the print settings. Using this code, you can print a specified sheet under the following conditions:

Fit to 1 page wide and 1 page tall
Print in A4 size
Set the entire sheet as the print area
Set print orientation to portrait

Print Settings Code

Sub ConfigurePrintSettings()
    Dim outputSheet As Worksheet

    ' 出力シートを設定
    Set outputSheet = ThisWorkbook.Sheets("sheet1") ' シート名を適宜変更してください

    ' 印刷設定
    With outputSheet.PageSetup
        .Zoom = False ' 拡大縮小を無効化
        .FitToPagesWide = 1 ' 横方向に1ページに収める
        .FitToPagesTall = 1 ' 縦方向に1ページに収める
        .Orientation = xlPortrait ' 縦向き
        .PaperSize = xlPaperA4 ' A4用紙サイズ
        .PrintArea = outputSheet.UsedRange.Address ' シート全体を印刷範囲に設定
    End With
End Sub

Code Explanation

1. Zoom Property

.Zoom = False This disables scaling during printing. This ensures the sheet content follows the specified page settings without being arbitrarily zoomed in or out.

2. FitToPagesWide and FitToPagesTall Properties

.FitToPagesWide = 1 .FitToPagesTall = 1 These settings fit the printout to one page in both width and height. By adjusting these properties, sheets that span multiple pages are automatically resized to fit.

3. Orientation Property

.Orientation = xlPortrait This sets the print orientation to “Portrait”. If you want “Landscape,” specify xlLandscape.

4. PaperSize Property

.PaperSize = xlPaperA4 This sets the paper size to A4. If you need to use other sizes, specify the appropriate property value, such as xlPaperLetter (Letter size).

5. PrintArea Property

.PrintArea = outputSheet.UsedRange.Address This sets the entire sheet as the print area. Using UsedRange automatically detects the range of cells currently in use and sets it as the print area.

Application: How to Apply to Multiple Sheets?

If you want to apply the same settings to multiple sheets, you can modify the code as follows:

Sub ConfigurePrintSettingsForAllSheets()
    Dim ws As Worksheet

    ' 全てのシートに対して設定
    For Each ws In ThisWorkbook.Sheets
        With ws.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .Orientation = xlPortrait
            .PaperSize = xlPaperA4
            .PrintArea = ws.UsedRange.Address
        End With
    Next ws
End Sub