Excel VBA: ExportAsFixedFormat PDF with Fit to Page (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

We can easily export sheets as PDFs in Excel by using the default option. But using VBA you can export with a lot of customizations, Fit to Page is one of them. So in this article, I’ll show 3 useful examples to learn how to use VBA for ExportAsFixedFormat PDF with Fit to Page in Excel.


Using VBA for ExportAsFixedFormat PDF with Fit to Page: 3 Examples

Let’s get introduced to our dataset first which represents some salesperson’s sales for 12 months from the year 2010 to 2015.


Macro 1. Use Command Button to Export PDF with Fit to page

In our very first example, we’ll set a Command button with assigned Macro then we’ll have to use the Export command of Excel to get PDF with Fit to Page.

Steps:

  • First, click as follows: Developer ➤ Insert
  • Then select the Square box from the ActiveX Controls section.

Soon after you will get a plus icon on your cursor.

Use Command Button for ExportAsFixedFormat PDF with Fit to Page

  • Drag the cursor anywhere in your sheet according to your desired size.
  • Then right-click your mouse and select View Code from the Context menu.

Use Command Button for ExportAsFixedFormat PDF with Fit to Page

  • Later, type the following codes in the appeared VBA window-
Private Sub CommandButton1_Click()
Dim xSheet As Worksheet, y_File As FileDialog, x_Fldr As String, xYesorNo, I, xNum As Integer
  Dim Otlk_Obj As Object, x_Email As Object, x_Used_Range As Range, xArrSht As Variant
  Dim xAddress_PDF As String, y_Str As String, xRng_Exp As Range, xLast_Rng As Range
     xArrSht = sheetsArr(Me)
     For I = 0 To UBound(xArrSht)
        On Error Resume Next
        Set xSheet = Application.ActiveWorkbook.Worksheets(xArrSht(I))
        If xSheet.Name <> xArrSht(I) Then
            MsgBox "Found no worksheet, exit operation:" & vbCrLf & vbCrLf & xArrSht(I), vbInformation, "PDF Fit to Page"
        Exit Sub
        End If
     Next
     Set y_File = Application.FileDialog(msoFileDialogFolderPicker)
     If y_File.Show = True Then
        x_Fldr = y_File.SelectedItems(1)
     Else
        MsgBox "Specify a folder to save PDF." & vbCrLf & vbCrLf & "Click OK to exit.", vbCritical, "Set a Folder"
        Exit Sub
     End If
     xYesorNo = MsgBox("If files with same name found, serial number will be added to the name" & vbCrLf & vbCrLf & "Press Yes to carry on, Press No to decline", _
     vbYesNo + vbQuestion, "Duplicate File Found")
     If xYesorNo <> vbYes Then Exit Sub
     For I = 0 To UBound(xArrSht)
        Set xSheet = Application.ActiveWorkbook.Worksheets(xArrSht(I))
        y_Str = x_Fldr & "\" & xSheet.Name & ".pdf"
        xNum = 1
        While Not (Dir(y_Str, vbDirectory) = vbNullString)
            y_Str = x_Fldr & "\" & xSheet.Name & "_" & xNum & ".pdf"
            xNum = xNum + 1
        Wend
        Set x_Used_Range = xSheet.UsedRange
        If Application.WorksheetFunction.CountA(x_Used_Range.Cells) <> 0 Then
            Set xLast_Rng = xSheet.Range("A" & xSheet.Rows.Count).End(xlUp)
            Set xRng_Exp = xSheet.Range(xLast_Rng.Offset(-26), xLast_Rng.Offset(, 7))
            xRng_Exp.ExportAsFixedFormat Type:=xlTypePDF, Filename:=y_Str, Quality:=xlQualityStandard
        End If
        xArrSht(I) = y_Str
     Next
     Set Otlk_Obj = CreateObject("Outlook.Application")
     Set x_Email = Otlk_Obj.CreateItem(0)
     With x_Email
        .Display
        .To = "[email protected]"
        .cc = "[email protected]"
        .Subject = "!!!"
        For I = 0 To UBound(xArrSht)
            .Attachments.Add xArrSht(I)
        Next
        If .DisplayEmail = False Then
        End If
     End With
End Sub
Private Function sheetsArr(uF As UserForm) As Variant
  Dim c As MSForms.Control, strCBX As String, arrSh
      For Each c In uF.Controls
            If TypeOf c Is MSForms.CheckBox Then
                If c.Value = True Then strCBX = strCBX & "," & c.Caption
            End If
      Next
      sheetsArr = Split(Mid(strCBX, 2), ",")
End Function
  • Then go back to your sheet.

Use Command Button for ExportAsFixedFormat PDF with Fit to Page

Keep these codes in the previous module too. I had to split it because of taking screenshots.

Use Command Button for ExportAsFixedFormat PDF with Fit to Page

Code Breakdown:

  • Here, I created a Private Sub CommandButton1_Click.
  • Then used sheetsArr which I created at the bottom of the existing code.
  • Next, I used the IF statement within the For Loop to check whether the Sheet is empty or not.
  • If the Sheet is not Empty then it will open a folder to Save the PDF
  • Here, to name the created PDF file and Fitted the dataset on one page while Exporting it from Excel Export.
  • I also used emails to send the created PDF.

  • Click the File option.

Use Command Button for ExportAsFixedFormat PDF with Fit to Page

  • Later, click Export.

Use Command Button for ExportAsFixedFormat PDF with Fit to Page

  • Finally, click as follows: Create PDF/XPS Documents ➤ Create PDF/XPS.

  • Choose your desired folder and name.
  • Then just press Publish.

Now see that the dataset is fitted to one page in the printed PDF successfully.


Macro 2. Export PDF with Fit to Page for a Specific Range

Here, we’ll set a range in our codes and then will export PDF according to that range with fitted width.

Steps:

  • Press Alt + F11 to open the VBA window.
  • Then click as follows to open a new module: Insert ➤ Module.

ExportAsFixedFormat PDF with Fit to Page for a Specific Range

  • Then write the following codes-
Sub PDF_FitToPage_2()
With ActiveSheet.PageSetup
    .Orientation = xlPortrait
    .PrintArea = "$B$2:$N$52"
    .PrintTitleRows = ActiveSheet.Rows(5).Address
    .Zoom = False
    .FitToPagesTall = False
    .FitToPagesWide = 1
End With
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="VBA ExportAsFixedFormat PDF Fit to Page", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    From:=1, _
    To:=5, _
    OpenAfterPublish:=True
End Sub
  • Next, go back to your sheet.

ExportAsFixedFormat PDF with Fit to Page for a Specific Range

Code Breakdown:

  • First, I created a Sub procedure PDF_FitToPage_2.
  • Then used the With statement and PageSetup to set the page layout for the active sheet.
  • Later, used ExportAsFixedFormat to set the file type, name, and other criteria for the PDF.

  • Open the Macros dialog box by clicking Developer ➤ Macros.

ExportAsFixedFormat PDF with Fit to Page for a Specific Range

  • Select the mentioned Macro name and press Run.

ExportAsFixedFormat PDF with Fit to Page for a Specific Range

Here’s our printed PDF with fitted width on one page.

Read More: Print Range to PDF with VBA in Excel


Macro 3. Export All Sheets in Separate PDF Files

In this example, I’ll use a Macro that will export all the sheets of my workbook in different PDF files. That means one file for one sheet.

Steps:

Sub PDF_FitToPage_3()
Dim xSheet As Worksheet, Sheet_Name$, My_FilePath$, N&
MyFilePath$ = "E:ExcelDemy" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & Format(Date, "MM-DD-YYYY")
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    On Error Resume Next
    For N = 1 To Sheets.Count
        Sheets(N).Activate
        Sheet_Name = ActiveSheet.Name
        Cells.Copy
        Workbooks.Add (xlWBATWorksheet)
        With ActiveWorkbook
            With .ActiveSheet
                .Paste
                .Name = Sheet_Name
                [A1].Select
                .PageSetup.Orientation = xlLandscape
                .PageSetup.Zoom = False
                .PageSetup.FitToPagesWide = 1
            End With
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=My_FilePath, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            .Close SaveChanges:=False
        End With
        .CutCopyMode = False
    Next
End With
Sheet1.Activate
End Sub
  • Next, go back to your sheet.

Export All Sheets in Different Different PDF Files with Fit to Page

Code Breakdown:

  • First, I created a Sub procedure PDF_FitToPage_3.
  • Then declared some variables.
  • Later, used For loop to count every sheet and print them.
  • Then completed the required page setups.

Export All Sheets in Different Different PDF Files with Fit to Page

Now have a look, Excel has printed all the sheets of my workbook as PDF with Fit to page.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Conclusion

I hope the procedures described above will be good enough to use VBA for ExportAsFixedFormat PDF with Fit to Page. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo