How to Create a VBA Macro to Copy Data Between Excel Workbooks
Question details
The user needs to build an Excel macro that automates opening weekly source files, filtering data by a specific promo week, and copying selected columns to corresponding destination sheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating weekly data compilation by copying specific columns from multiple source workbooks into a master destination workbook.
- Observed behavior
- The user wants to achieve a one-click automated process that dynamically prompts for a promo week, locates the correct files, and transfers the filtered data without manual copying.
Ensure that all your weekly source files are saved in the same folder as your destination workbook, and remember to save your master file as an Excel Macro-Enabled Workbook (.xlsm).
Create a Dynamic VBA Macro to Filter and Copy Data
Use a comprehensive VBA script to prompt for a promo week, automatically locate and open the source files, apply the filter, and copy the required columns to the destination sheets.
This VBA method uses the built-in Dir() function or FileSystemObject to locate source workbooks in the current folder. It opens each workbook, filters the data based on your inputted promo week, and copies the visible cells to the master workbook. Be sure to adjust the filename patterns and column letters to match your specific layout.
Press ALT + F11 to open the Visual Basic for Applications (VBA) Editor in your destination Excel workbook.
In the top menu, click 'Insert' and select 'Module' to create a new blank script window.
Write your macro to declare workbook and worksheet variables, and use an InputBox command to ask the user for the target promo week (e.g., promoWeek = InputBox("Enter Promo Week:")).
Use a While loop with the Dir() function to iterate through all matching Excel files in the folder. For each file found, use Workbooks.Open to open it in the background.
Apply the AutoFilter method to the source data range based on the inputted promo week. Then, use Range.SpecialCells(xlCellTypeVisible).Copy and paste it into the next empty row of your destination sheet.
Include the command ActiveWorkbook.Close SaveChanges:=False to close the source file without altering the original data, then end your loop.
Automate Workbooks Seamlessly with WPS Spreadsheet
WPS Office offers robust support for Excel macros and VBA scripts, allowing you to easily automate complex tasks like copying data between workbooks while providing a smooth, lightweight user experience.
- 1. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm master workbook.
- 2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon menu to access macro features.
- 3. Edit or Create the Script: Click 'Visual Basic' to paste or modify your data-copying VBA code exactly as you would in standard Excel.
- 4. Run the Macro: Click 'Macros', select your script, and hit 'Run' to execute the automated filtering and copying process across your workbooks.
Frequently Asked Questions
Why am I getting a 'Subscript out of range' error when running the macro?
This error usually occurs if the sheet name or workbook name referenced in the VBA code doesn't exactly match the actual names in your file. Double-check your spelling and ensure the source files are properly named and located in the correct directory.
How can I attach this VBA macro to a clickable button?
Go to the Developer tab, click 'Insert', and select 'Button (Form Control)'. Draw the button on your worksheet, and a dialog box will appear asking you to assign a macro. Select your new data-copying macro and click OK.
Can I filter by multiple criteria before copying the columns?
Yes, you can modify the AutoFilter method in your VBA code to include multiple criteria by defining an array of values for the Criteria1 parameter, or by adding a second criteria parameter for a different column.
Does the destination workbook need to be closed during this process?
No, the destination workbook (where you are housing and running the macro) must be open. The macro will systematically open the background source workbooks, copy the data into your active destination workbook, and then close the source files.
