VBA & Macro Problems

How to Automatically Copy and Rename Excel Workbooks using VBA

Bushra Parveen Oct 7, 2026 869 views

Question details

The user needs to automate the creation and renaming of approximately 700 Excel workbooks for individual employees.

How to Automatically Copy and Rename Excel Workbooks using VBA

Product: Excel
Device & OS: not provided
Scenario: Distributing individual copies of a master workbook to hundreds of employees based on a list of names.
Observed behavior: Manually copying and renaming each workbook takes an excessive amount of time and is highly prone to error.

Before you start

Before running any macro, ensure you have created a dedicated destination folder for the new files and verified that your list of employee names does not contain any characters that are invalid in Windows file names (such as /, \, *, ?, ", <, >, or |).

Solution 1Recommended

Use a VBA Macro to Automate Copying and Renaming

Create a VBA script that loops through your employee list, saving a personalized copy of the workbook for each name in a designated folder.

This method is highly efficient for batch-processing hundreds of files. You will need to define the range containing the employee names and specify the exact folder path where the new files will be stored.

Open the VBA Editor

Press ALT + F11 on your keyboard, or navigate to the Developer tab on the ribbon and click 'Visual Basic'.

Insert a New Module

In the VBA editor, click 'Insert' from the top menu and select 'Module'. This will open a blank window where you can paste your macro code.

Configure the Macro Code

Paste your looping VBA script. Ensure you update the code's variables to point to your specific employee name range (e.g., Range("A2:A700")) and define the correct destination folder path (e.g., "C:\Users\Admin\Desktop\EmployeeWorkbooks\").

Test and Run the Macro

Press F5 or click the 'Run' button. It is highly recommended to test the macro on a small sample of 5 to 10 employee names first to ensure the files are saving and renaming correctly before processing all 700.

Use a VBA Macro to Automate Copying and Renaming

Check for Invalid Characters: If the macro encounters an employee name with an invalid character (like a slash or colon), it will result in a runtime error and stop processing. Clean your data before execution.

Solution 2

Use SharePoint for Collaborative Access

If individual offline files are not strictly required, hosting a single master workbook on a collaborative platform like SharePoint can eliminate the need to copy files.

Efficient Spreadsheet Automation

Automate Your Workbooks with WPS Spreadsheets

WPS Office provides robust support for advanced spreadsheet operations, including data management, batch processing, and macro automation. With WPS Spreadsheets, you can easily organize employee lists, execute scripts, and manage hundreds of files seamlessly.

1. Download and Install: Get the free version of WPS Office from the official website and install it on your device.
2. Open Your Master File: Launch WPS Spreadsheets and open the workbook containing your employee data and macro code.
3. Execute Automation: Navigate to the Developer tab, open the Macro editor, and run your batch-copy scripts with high performance.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) formats.Free, lightweight, and fast document processing.Intuitive Developer tools for managing large datasets and running macros.

microsoft office alternative - wps office

Frequently Asked Questions

What characters are invalid for saving Excel workbooks in Windows?

Windows file names cannot contain the following characters: \, /, :, *, ?, ", <, >, and |. If your macro tries to save a file with a name containing any of these, it will trigger an error and halt the process.

Why does my VBA macro stop halfway through the employee list?

This usually happens if there is an empty cell in your named range, an invalid character in an employee's name, or if the file path length exceeds the Windows maximum limit. Review your list at the point where the macro stopped to find the anomaly.

Can I use VBA to save the copied workbooks as PDF files instead?

Yes. You can modify your VBA code to use the 'ExportAsFixedFormat' method rather than 'SaveCopyAs'. This will allow you to generate and uniquely name PDF documents based on your employee list.

Excel & Data Problems