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.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
3 Examples of Using VBA for ExportAsFixedFormat PDF with Fit to Page
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.
- 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.
- 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.
- 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.
Keep these codes in the previous module too. I had to split it because of taking screenshots.
- 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.
- Later, click Export.
- 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.
Read More: Print Range to PDF with VBA in Excel (5 Easiest Examples)
- How to Convert Excel to PDF without Converter (5 Effective Methods)
- Print to PDF Using Macro Button in Excel (5 Macro Variants)
- How to Save Excel as PDF without Cutting Off (4 Suitable Ways)
- Excel VBA to Print As PDF and Save with Automatic File Name
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.
- Press Alt + F11 to open the VBA window.
- Then click as follows to open a new module: Insert ➤ Module.
- 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.
- 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.
- Select the mentioned Macro name and press Run.
Here’s our printed PDF with fitted width on one page.
Read More: Export Excel to PDF with Hyperlinks (2 Quick Methods)
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.
- Follow the first two steps from the second example to insert a VBA module.
- Then write the following codes-
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.
- 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.
- Later, follow the fifth step from the second example to open the Macro dialog box.
- Select the specified Macro and press Run.
Now have a look, Excel has printed all the sheets of my workbook as PDF with Fit to page.
Read More: Excel Macro: Save as PDF with Date in Filename (4 Suitable Examples)
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.
- How to Convert Excel to PDF without Losing Formatting (5 Effective Ways)
- Excel VBA: Create Invoice and Save PDF Format (with Quick Steps)
- How to Save Multiple Excel Sheets as One PDF (2 Easy Methods)
- Excel Macro to Save as PDF with Filename from Cell Value (2 Examples)
- How to Convert Excel to PDF with All Columns (5 Suitable Ways)