How to Automatically Add Missing Project Rows Between Excel Workbooks Using VBA
Question details
The user needs a way to automatically append missing project names from a source workbook to a destination workbook while creating blank, editable rows for manual monthly data entry.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Keeping a master project list updated across multiple workbooks without overwriting existing manual entries, a scenario where Power Query falls short.
- Observed behavior
- Power Query refreshes query results but does not natively insert editable rows for manual entry while preserving existing adjacent data. A VBA solution is required.
Ensure both your source and destination workbooks are saved in a macro-enabled format (.xlsm) and that you have enabled macros in your spreadsheet security settings.
Use a VBA Macro to Compare Workbooks and Append Missing Rows
Run a custom VBA script that loops through the source workbook, checks for existing project names in the destination sheet, and appends any missing projects to the bottom.
While Power Query is excellent for retrieving and refreshing data, it typically overwrites tables and is not ideal when you need to maintain adjacent manual entry columns (like monthly data).
VBA can solve this by programmatically identifying the last row, searching for each project name, and only adding rows that do not yet exist in the destination file.
Open both the source workbook containing the master list and the destination workbook where you want to append the new project rows.
In the destination workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor. Click 'Insert' from the top menu and select 'Module'.
Paste your VBA code into the module. Ensure your script includes a For Each loop that checks the source range against the destination using the .Find method, and appends the value using 'lastDst = lastDst + 1' if 'found Is Nothing'.
Update the 'Set src = Workbooks("SourceWorkbook.xlsx")' and 'Set dst' lines in your code to match the exact file names and sheet names of your actual workbooks.
Press F5 or click the Run button to execute the macro. It will scan the source, append any missing project names to the destination, and clear the adjacent monthly cells for new data entry.

Use WPS Spreadsheet for Seamless VBA and Macro Operations
WPS Office offers robust support for VBA macros, allowing you to automate repetitive tasks like syncing project lists across workbooks with ease and high performance.
- 1. Download and Install WPS Office: Download WPS Office for free from the official website and install it on your computer.
- 2. Enable VBA Support: Open WPS Spreadsheet. Depending on your version, ensure the VBA module is installed and enabled to allow macro execution.
- 3. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'VBA Editor' to manage or write your custom scripts.
- 4. Run Your Automation: Execute your project row comparison macro directly within WPS Spreadsheet to seamlessly update your workbooks.

Frequently Asked Questions
Why use VBA instead of Power Query for appending project rows?
Power Query is designed to retrieve and refresh entire datasets, which often overrides the table structure. If you need to append rows but leave adjacent columns blank for manual monthly data entry, VBA is more flexible because it preserves existing manual inputs and only alters the required cells.
Can I automate the macro to run when the workbook opens?
Yes, you can trigger the macro automatically. Open the VBA editor, double-click 'ThisWorkbook', and use the 'Workbook_Open()' event to call your update procedure. This ensures data is synced immediately upon opening the file.
What happens if my source workbook is closed when the macro runs?
The VBA macro requires the source workbook to be open to read its values. If it is closed, the script will result in a 'Subscript out of range' error. You can prevent this by adding 'Workbooks.Open("C:\Path\To\SourceWorkbook.xlsx")' at the beginning of your VBA code.




