VBA & Macro Problems

How to Use Excel VBA to Create Separate Reports for Multiple Names

Adam Davis Sep 29, 2026 869 views

Question details

The user needs to automatically generate and export individual Excel or PDF reports for multiple people based on a list of names.

How to Use Excel VBA to Create Separate Reports for Multiple Names

Product: Excel
Device & OS: not provided
Scenario: Generating individual statement reports from a central summary sheet without doing it manually for each person.
Observed behavior: A VBA macro is required to loop through the names, update the report template dynamically, and export the files automatically using the person's name as the file name.

Before you start

Ensure that your workbook contains a fully configured Statement report sheet that updates automatically when a specific control cell is changed, and verify that macros are enabled in your spreadsheet software.

Solution 1Recommended

Create a VBA Macro to Loop and Export Reports

Write a VBA script to iterate through a list of names, update the report control cell, and export each resulting sheet as a PDF or workbook.

This method involves writing a custom VBA macro. The macro changes the cell that controls your Statement report, triggers a data refresh, and then saves the active sheet as a separate file using the current name.

Open the VBA Editor

Press 'Alt + F11' in your workbook to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' from the top menu bar and select 'Module' to create a blank script window.

Write the Loop Script

Write a 'For Each' loop that iterates through the specific range of names on your Summary sheet. Inside the loop, set the value of the control cell on your Statement sheet to the current name.

Add the Export Command

Inside the loop, immediately after updating the control cell, use the 'ExportAsFixedFormat' method to save the Statement sheet as a PDF. Concatenate your output folder path with the current cell value to dynamically name the file.

Run the Macro

Press 'F5' or click the 'Run' button to execute the macro. Check your designated output folder to verify that the separate reports have been generated.

Create a VBA Macro to Loop and Export Reports

Customize Your Variables: Before running the macro, ensure you customize the script with your specific starting cell for the name list, the correct control cell reference, and an exact folder path ending with a backslash.

Efficient Data Processing with WPS Office

Automate Your Reports Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful data processing capabilities, including advanced macro support and formulas. You can easily automate repetitive tasks like generating individual reports quickly and securely.

1. Open Your Workbook: Launch WPS Office and open your .xlsm file containing the summary and statement sheets.
2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon to access Macros and the built-in VBA/JS Macro editor.
3. Run Your Automation: Insert your loop and export script, customize your folder paths, and run it to instantly generate separate PDFs for all names in your list.

Fully compatible with Microsoft Excel (.xlsx, .xls, .xlsm formats)Supports advanced formulas and macros for seamless data automationBuilt-in PDF converter to export your reports instantlyLightweight, fast-loading, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro saving all files with the exact same data?

This usually happens if the report sheet is not recalculating before the export step. Ensure your macro updates the control cell properly, and consider adding an 'Application.Calculate' line inside your loop right before the export command.

Can I export the reports as separate Excel workbooks instead of PDFs?

Yes. Instead of using 'ExportAsFixedFormat', you can use the 'Copy' method on the Statement sheet to copy it into a new workbook, and then use 'SaveAs' to save that new workbook as an Excel file using the person's name.

How do I specify a dynamic file path for the exported reports?

In your macro script, you can concatenate the folder path string with the cell value containing the name. For example: 'FolderPath & cell.Value & ".pdf"'. Make sure the FolderPath string ends with a backslash (\).

Excel & Data Problems