How to Automatically Create 2000 Excel Workbooks from One Data Workbook
Question details
The user needs to automatically generate thousands of individual Excel workbooks based on rows from a master data workbook.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Generating bulk individual files, such as certificates or standardized reports, based on a master database and a single formatted template.
- Observed behavior
- The goal is to automate a process that loops through 2,000 database rows, populates a template sheet for each record using a numerical index, and saves every result as a distinct workbook file without manual data entry.
Before running the macro, ensure your master data is organized with a unique numerical index column and create a dedicated folder on your local drive to securely store the newly generated workbooks.
Use a VBA Loop with INDEX and MATCH Formulas
Combine Excel lookup formulas with a VBA macro to automate the calculation and saving of individual files based on your template.
This method involves using a helper cell in your template to fetch data row by row. A VBA macro simply iterates through the index values, updates the helper cell, forces the formulas to recalculate, and saves a separate copy of the workbook for each index.
Insert a new column in your source data worksheet and number the rows sequentially (e.g., 1 to 2,000) so each record has a unique ID.
In your template worksheet, designate a specific cell (such as B1) to act as the index control cell. Type '1' in this cell to prepare for the first record.
Populate your template's data fields using formulas like =INDEX(Data!B:B,MATCH($B$1,Data!$A:$A,0)). This ensures the template fetches the correct row data from your database whenever the helper cell (B1) changes.
Press Alt + F11 to open the VBA Editor, insert a new Module, and write a 'For' loop that iterates from 1 to 2,000. Inside the loop, instruct VBA to write the current loop counter value into your helper cell.
Within the same VBA loop, add the command Application.Calculate to force the formulas to update. Then, use ActiveWorkbook.SaveCopyAs combined with a dynamically generated file path to save the populated template as a new workbook.
Generate Bulk Excel Workbooks Easily in WPS Office
WPS Spreadsheet fully supports advanced array formulas like INDEX and MATCH, alongside a robust VBA macro environment to automate repetitive tasks like bulk file generation without application lag.
- 1. Open your files in WPS Spreadsheet: Launch WPS Office and open your master database and template files.
- 2. Configure your lookup formulas: Set up your INDEX and MATCH functions in the template, pointing them directly to your helper cell.
- 3. Access Developer Tools: Navigate to the Developer tab on the top ribbon and click on 'VBA Editor' to access the scripting environment.
- 4. Execute your Macro: Paste your VBA looping script into a new module and click Run to automatically generate and save all your workbooks.
Frequently Asked Questions
Why is the template not updating when the VBA loop changes the index?
Ensure that your calculation mode is set to automatic. If it is set to manual, you must explicitly include the 'Application.Calculate' command in your VBA script immediately after changing the index cell and before saving the copy.
Can I save the generated workbooks as PDFs instead of Excel files?
Yes. You can modify your VBA script to use 'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF' instead of the 'SaveCopyAs' method, which will instantly generate individual PDF files for each template record.
How do I prevent the screen from flickering during macro execution?
Add 'Application.ScreenUpdating = False' at the very beginning of your VBA macro code, and set it back to 'True' right before the macro ends. This will significantly speed up the looping process and stop visual screen flickering.
