VBA & Macro Problems

How to Automatically Add Missing Project Rows Between Excel Workbooks Using VBA

Algirdas JasaitisAlgirdas Jasaitis Nov 11, 2026 869 views

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.

How to Automatically Add Missing Project Rows Between Workbooks with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Both Workbooks

Open both the source workbook containing the master list and the destination workbook where you want to append the new project rows.

2
Access the VBA Editor

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'.

3
Insert the Comparison Macro

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'.

4
Configure Workbook Names

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.

5
Run the Macro

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 a VBA Macro to Compare Workbooks and Append Missing Rows
Automating the Process: You can call this macro from the 'Workbook_Open' event in 'ThisWorkbook' so it runs automatically every time the destination workbook is opened. Just ensure the source workbook is open or programmatically opened by the script first.
Advanced Macro Support

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. 1. Download and Install WPS Office: Download WPS Office for free from the official website and install it on your computer.
  2. 2. Enable VBA Support: Open WPS Spreadsheet. Depending on your version, ensure the VBA module is installed and enabled to allow macro execution.
  3. 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. 4. Run Your Automation: Execute your project row comparison macro directly within WPS Spreadsheet to seamlessly update your workbooks.
Fully compatible with Microsoft Excel .xlsm and .xlsb formatsBuilt-in VBA editor for writing, editing, and debugging macrosLightweight application with high performance for large datasetsFree to use with comprehensive spreadsheet features
microsoft office alternative - wps office

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.