How to Automatically Copy and Update Data from Multiple Excel Sheets Using VBA
Question details
The user needs a method to automatically copy and refresh columns of data from multiple source sheets into a single destination sheet without using VLOOKUP or dragging manual formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating hundreds of rows of data from multiple worksheets into a master sheet for up-to-date reporting.
- Observed behavior
- The user wants the destination sheet to update automatically with the newest data whenever the source data in Sheet1 or Sheet2 is modified.
Ensure you save your workbook as a Macro-Enabled Workbook (.xlsm) before adding VBA code, otherwise your automation scripts will be lost when you close the file.
Use a VBA Worksheet_Activate Event for Automatic Refresh
This method runs a short VBA script every time you switch to the destination sheet, copying the latest data from your source sheets without needing manual formulas.
By using the Worksheet_Activate event, your destination sheet (Sheet3) will automatically pull the newest data from Sheet1 and Sheet2 whenever you click on its tab. This completely eliminates the need for complex VLOOKUPs or manually dragging formulas across hundreds of rows.
Right-click the destination sheet tab (e.g., Sheet3) at the bottom of your workbook and select 'View Code' to open the VBA Editor.
In the code window, select 'Worksheet' from the left dropdown menu at the top, and choose 'Activate' from the right dropdown menu.
Write the VBA code to clear the old data in Sheet3 and copy the specific ranges from Sheet1 and Sheet2 (for example, use 'Sheets("Sheet1").Range("A:A").Copy Destination:=Sheets("Sheet3").Range("A:A")'). Customize the sheet names and ranges to match your actual workbook.
Close the VBA Editor. Switch to Sheet1 or Sheet2, and then click back to Sheet3. The code will execute automatically and refresh your data.
Automate Your Worksheets with WPS Spreadsheet
WPS Spreadsheet provides robust support for VBA and macros, allowing you to seamlessly automate data consolidation across multiple sheets. It supports standard Excel macro files (.xlsm) so your existing scripts work right out of the box.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet application.
- 2. Enable the Developer Tab: Go to the Options menu to enable the Developer Tools tab if it is not already visible on your ribbon.
- 3. Access the VBA Editor: Click the 'Developer' tab and select 'Visual Basic' to enter your Worksheet_Activate code.
- 4. Save as .xlsm: Go to Menu > Save As, and choose the 'Excel Macro-Enabled Workbook (*.xlsm)' format to keep your code active.
Frequently Asked Questions
Why isn't my VBA code running when I switch sheets?
Ensure you have placed the code in the specific Worksheet module (e.g., Sheet3) rather than a standard general Module. Also, check that macros are enabled in your Excel or WPS security settings.
Can I use Power Query instead of VBA for combining multiple sheets?
Yes, Power Query is a powerful alternative for merging data from multiple sheets without code. You can use the 'Get Data' feature to append sheets and simply click 'Refresh All' to update the master table when source data changes.
Will the Worksheet_Activate event slow down my Excel file?
If you are copying entire columns (like A:A) with millions of rows across multiple sheets, it might cause lag each time you switch tabs. It is highly recommended to copy only the used range (e.g., A1:A500) to optimize performance.
