VBA Guide: Copy Worksheets from Multiple Excel Files in a Folder
Question details
The user needs a VBA macro to select a monthly folder, loop through all Excel files, and copy a specific worksheet (e.g., 'Report' or 'Template') into a single new workbook while applying safe renaming logic.

- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Aggregating monthly reports or templates from multiple workbooks stored in a dynamically selected folder into a single master workbook.
- Observed behavior
- The macro must handle missing worksheets by falling back to an alternative sheet name without breaking execution, and ensure all newly copied sheet names are valid and within Excel limits.
Before running the macro, ensure that all target Excel files are placed within your selected monthly folder and that your master workbook is saved as a Macro-Enabled Workbook (.xlsm).
Implement VBA Macro with Folder Picker and Fallback Error Handling
Create a robust VBA script that prompts the user to select a directory, iterates through all Excel workbooks, and dynamically copies target worksheets with a built-in fallback.
This solution utilizes the FileDialog object to let users dynamically choose a folder instead of hardcoding the path. It also implements controlled error handling so that if a 'Report' worksheet is missing, the script seamlessly falls back to copying the 'Template' worksheet.
Initialize Application.FileDialog(msoFileDialogFolderPicker) to capture the user's selected path. Store this in a String variable and ensure you concatenate a trailing backslash ('\') at the end of the path string.
Use the Dir function (e.g., fileName = Dir(folderPath & "*.xls*")) to loop through all Excel files within the selected directory, using a Do While loop to open each workbook sequentially.
Before the copy command, type 'On Error Resume Next' to prevent runtime crashes. Attempt to copy the 'Report' sheet. If Err.Number <> 0, use Err.Clear and copy the 'Template' sheet instead. Finally, reset the error handler with 'On Error GoTo 0'.
Extract the desired sheet name from cell C3 of the newly copied worksheet. Use the Replace function to strip invalid characters (such as '/', '\', '?', '*', '[', ']') and wrap the result in Left(name, 31) to enforce the 31-character limit.
To keep the master workbook tidy, use Application.DisplayAlerts = False to suppress confirmation dialogs, delete any blank default worksheets, and then set Application.DisplayAlerts = True again.

Run and Edit VBA Macros Seamlessly in WPS Spreadsheet
WPS Office provides robust built-in support for VBA macros, allowing you to execute, edit, and troubleshoot complex scripts like looping through folders and copying worksheets just as you would in Microsoft Excel.
- 1. Access Developer Tools: Open WPS Spreadsheet and navigate to the 'Developer' tab on the top ribbon.
- 2. Open the VBA Editor: Click on 'Visual Basic' to launch the VBA editor where you can manage your code.
- 3. Insert Your Macro: Insert a new module and paste your folder-looping and worksheet-copying macro code.
- 4. Run and Automate: Run the macro directly from the editor or assign it to a button to automatically aggregate your monthly reports.

Frequently Asked Questions
Why does my VBA macro crash when a worksheet is missing?
By default, VBA throws a 'Subscript out of range' runtime error if you attempt to reference a worksheet that doesn't exist in the active workbook. You must use 'On Error Resume Next' right before the copy command to bypass the error, handle the fallback, and then reset error handling with 'On Error GoTo 0'.
How do I ensure copied worksheet names do not cause errors?
Excel restricts worksheet names to a maximum of 31 characters and forbids certain special characters like square brackets, slashes, and asterisks. It is best practice to write a helper step in your VBA code to use the Replace function to strip these characters and the Left() function to truncate the string length.
Can I filter the macro loop to only open specific file types like .xlsx?
Yes, you can modify the Dir function pattern. Instead of using '*.xls*', you can specify 'Dir(folderPath & "*.xlsx")', which restricts the macro to only process standard Excel workbooks and ignores macro-enabled or older file formats.




