VBA & Macro Problems

How to Create an Excel VBA Button to Generate Statements as PDF or Excel

Huma Ashraf Ch Sep 29, 2026 870 views

Question details

The user wants to automate the creation of individual statement files in PDF or Excel format based on selected names using a VBA macro triggered by a button.

Create an Excel VBA Button to Generate Statements as Excel or PDF

Product: Excel
Device & OS: not provided
Scenario: Automating financial or data statement generation from a master summary sheet to individual templates.
Observed behavior: The user needs a structured method to select names, populate templates, and export them as specific file types using a VBA macro button.

Before you start

Ensure the Developer tab is enabled in your spreadsheet ribbon. You should also have your 'SUMMARY' data sheet and 'STATEMENT' template sheet fully formatted before writing the VBA code.

Solution 1Recommended

Insert a Form Control Button and Assign the Generation Macro

Add an interactive button to your summary sheet that triggers a VBA macro to handle data extraction, template population, and file saving.

A practical implementation requires VBA code tailored to your workbook’s specific layout. The macro should prompt the user to select names, filter or locate the matching records in the summary sheet, populate the statement sheet, and save or export each statement in the chosen format.

Enable the Developer Tab

Go to the Developer tab on your ribbon. If it is not visible, enable it by going to File > Options > Customize Ribbon and checking the 'Developer' box.

Insert a Button

On the SUMMARY sheet, click on 'Insert' in the Developer tab, choose 'Button (Form Control)', and draw the button onto your sheet.

Open the VBA Editor

When the 'Assign Macro' dialog appears after drawing the button, click 'New' to open the VBA Editor.

Write the Macro Code

Write a script utilizing Application.InputBox with Type:=8 to prompt for name selection. Loop through the selected names to populate the STATEMENT template. Use ExportAsFixedFormat for PDFs or SaveCopyAs for saving as .xlsx files.

Insert a Form Control Button and Assign the Generation Macro

Macro Security: You must save your workbook as a Macro-Enabled Workbook (.xlsm) to ensure your VBA code is preserved after closing the file.

Automate with WPS Spreadsheet

Automate Statement Generation with WPS Spreadsheet

WPS Office fully supports VBA macros, allowing you to create automated buttons, run scripts, and export statements to Excel or PDF formats seamlessly and for free.

1. Open your Workbook: Launch WPS Spreadsheet and open your summary and statement workbook.
2. Insert a Button: Go to the Developer tab and click 'Insert' to add a Form Control Button.
3. Add your VBA Code: Click 'Macros' or press ALT+F11 to open the built-in VBA editor and paste your statement generation script.
4. Generate Statements: Click the newly created button to run the macro and seamlessly generate your Excel or PDF statements.

Full compatibility with Microsoft Excel VBA macros and .xlsm formats.Easily insert Form Controls to automate repetitive statement generation.Built-in PDF exporter to save statements directly as PDF using VBA.Free, lightweight, and fast alternative to heavy spreadsheet software.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the Application.InputBox Type:=8 important in this VBA macro?

Setting Type:=8 in the InputBox method specifically tells the spreadsheet program to expect a Range object. This allows the user to select cells directly with their mouse instead of manually typing the cell addresses.

How do I export a specific sheet as a PDF using VBA?

You can use the `Sheet.ExportAsFixedFormat Type:=xlTypePDF` method in your VBA code. You will need to specify the file path and name to save the populated statement template directly as a PDF.

Why does my VBA button stop working when I reopen the file?

This usually happens if the file was saved as a standard Workbook (.xlsx) instead of a Macro-Enabled Workbook (.xlsm). Standard workbooks strip out VBA code upon saving. Always save macro-integrated files as .xlsm.

Excel & Data Problems