Create Excel Buttons to Print a Range or Save It as PDF
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.

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.




