VBA & Macro Problems

Create Excel Buttons to Print a Range or Save It as PDF

Elise WilliamsElise Williams Nov 13, 2026 868 views

Question details

Create separate Excel worksheet buttons that print a defined cell range and save that range as a PDF.

Product
Microsoft Excel
Device & OS
not provided
Scenario
Automate repeated printing and PDF export of the same worksheet area using buttons assigned to VBA procedures.
Observed behavior
Each button should run its assigned macro: one sends the specified range to the printer, and the other exports that range to a PDF file.
Before you start

Use desktop Excel, since Excel for the web cannot run VBA macros. Identify the worksheet name and target cell range, and save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).

Solution 1Recommended

Assign separate print and PDF export macros to worksheet buttons

Use this approach to create two one-click controls for a fixed worksheet range.

The examples below use Sheet1 and cells A1:F20. Replace those values in both procedures with your actual worksheet name and range.

The print macro sends the range directly to the active printer. The PDF macro prompts for a filename and location before exporting.

1
Open the VBA editor

In desktop Excel, open the Developer tab and click Visual Basic. If Developer is hidden in Windows, go to File > Options > Customize Ribbon and select Developer. On macOS, enable it under Excel > Preferences > Ribbon & Toolbar.

2
Insert a standard module

In the VBA editor, select your workbook in Project Explorer, then choose Insert > Module. Paste the following procedures into the module, not into a worksheet code window.

3
Add the print macro

Paste this code and replace Sheet1 and A1:F20 as needed: Sub PrintTargetRange() On Error GoTo PrintFailed ThisWorkbook.Worksheets("Sheet1").Range("A1:F20").PrintOut Copies:=1 Exit Sub PrintFailed: MsgBox "Printing failed: " & Err.Description, vbExclamation End Sub For a preview instead of immediate printing, replace .PrintOut Copies:=1 with .PrintPreview.

4
Add the PDF export macro

Paste this second procedure into the same module: Sub SaveTargetRangeAsPDF() Dim filePath As Variant On Error GoTo ExportFailed filePath = Application.GetSaveAsFilename( _ InitialFileName:="RangeExport.pdf", _ FileFilter:="PDF Files (*.pdf), *.pdf") If VarType(filePath) = vbBoolean Then Exit Sub If LCase$(Right$(CStr(filePath), 4)) <> ".pdf" Then filePath = CStr(filePath) & ".pdf" End If ThisWorkbook.Worksheets("Sheet1").Range("A1:F20").ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=CStr(filePath), _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=True, _ OpenAfterPublish:=True Exit Sub ExportFailed: MsgBox "PDF export failed: " & Err.Description, vbExclamation End Sub This exports the specified range without relying on a previously configured worksheet print area.

5
Create and assign the buttons

Return to the worksheet and choose Insert > Shapes. Draw a shape, label it Print Range, then right-click it and choose Assign Macro > PrintTargetRange. Create a second shape labeled Save Range as PDF and assign SaveTargetRangeAsPDF. On Windows, you can also use Developer > Insert > Button under Form Controls.

6
Check the layout and test both actions

Use Page Layout > Orientation, Margins, and Scale to Fit to adjust the output. Save the workbook as *.xlsm, then click Print Range to test printing and Save Range as PDF to choose a destination. Enable macros only if you trust the workbook and its code.

Keep buttons outside the output range: Place the controls outside A1:F20, or outside whichever range you specify, so they do not appear in the printed pages or exported PDF. Page layout settings can cause the range to span multiple pages.
Free Microsoft Office alternative

Print spreadsheet ranges and create PDFs with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for easy migration. Use WPS Spreadsheet for manual range printing and PDF output; it does not fix Excel VBA issues, and support for these button macros depends on the WPS edition and platform.

Print selected spreadsheet cells without building VBA buttons.Work with Microsoft Excel .xlsx and .xls files.Use familiar worksheet, page layout, and printing tools.Create PDF output from spreadsheets with built-in export tools or an available PDF printer.
microsoft office alternative - wps office

Frequently Asked Questions

Can the button print whichever cells I select?

Yes. In the VBA editor, replace the fixed-range print statement with Selection.PrintOut Copies:=1. Select the cells before clicking the button, and use this only when the selection is a cell range rather than a chart or shape.

Why do the macros disappear after I save the workbook?

The .xlsx format cannot store VBA code. Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). If Excel warns that VBA cannot be saved in the selected format, change the file type before continuing.

Can I preview the range before sending it to the printer?

Yes. Open Developer > Visual Basic and replace .PrintOut Copies:=1 in PrintTargetRange with .PrintPreview. Clicking the assigned button will then open a preview instead of printing immediately.

Why is the PDF split across several pages?

PDF export uses the worksheet's page layout settings. Open Page Layout > Scale to Fit and set Width to 1 page. Set Height to Automatic to preserve readability, or 1 page if the entire range must fit on a single page.

Will these buttons work in Excel for the web or WPS Spreadsheet?

Excel for the web cannot run VBA macros; open the file in desktop Excel to use the buttons. WPS VBA support varies by edition and platform, so do not assume the macros will run there. For manual output in WPS Spreadsheet, select the cells and use File > Print, then choose the selection option if available.