VBA & Macro Problems

How to Copy Excel Data by Matching Headers Using VBA

Huma Ashraf Ch Oct 19, 2026 869 views

Question details

The user needs an Excel VBA macro to copy data from a source workbook to a template workbook by matching column header names instead of exact column positions. The solution must handle protected worksheets, avoid overwriting unrelated columns, and trim copied data except for the "Invoice No." column.

How to Copy Excel Data by Matching Headers Using VBA

Product: Excel
Device & OS: not provided
Scenario: Migrating or merging data between two workbooks where the destination template has columns in a different order and includes protected header cells.
Observed behavior: Standard copy-paste or address-based VBA copying transfers data to the wrong columns due to differing layouts. Additionally, the macro fails to run because the template's destination worksheet is protected.

Before you start

Ensure you have the correct passwords for any protected worksheets and verify which row contains your headers before modifying the VBA script.

Solution 1Recommended

Use Scripting.Dictionary in VBA to Map Headers and Copy Data

Use a VBA dictionary object to map the source and destination columns by their header names, temporarily unprotect the sheet, and selectively copy and trim the data.

By utilizing a dictionary object in VBA, you can dynamically store the column indexes for each header name in both the source and template workbooks. This ensures that data is routed to the correct destination column even if the layout changes.

To bypass errors caused by protected template sheets, the macro must unprotect the worksheet before writing data and reapply the protection once the transfer is complete. A specific condition can be added to the loop to skip the Trim function for the "Invoice No." column.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. Go to Insert > Module to create a new blank module.

Unprotect the Destination Worksheet

At the beginning of your script, declare your workbook and worksheet variables. Use the command destWs.Unprotect "YourPassword" to temporarily disable protection on the template sheet so the macro can read headers and write data.

Map Headers Using Scripting.Dictionary

Initialize two dictionary objects (e.g., CreateObject("Scripting.Dictionary")). Loop through the header row of both the source and destination worksheets. Add each header name as the dictionary 'Key' and its column index as the 'Value'.

Loop and Copy Data

Iterate through the data rows in the source worksheet. For each row, loop through the dictionary keys. If a source header matches a destination header, copy the cell value across. Use an If statement to apply the Trim() function only when the header name is not equal to "Invoice No.".

Reprotect and Close

After the data loop finishes, use destWs.Protect "YourPassword" to secure the template again. Finally, close the workbooks using srcWb.Close SaveChanges:=False and destWb.Close SaveChanges:=True.

Use Scripting.Dictionary in VBA to Map Headers and Copy Data

Header Row Alignment: If your headers are located in a row other than Row 1 (e.g., Row 5), make sure to update the row index references in your VBA loop (like changing Cells(1, i) to Cells(5, i)).

Run VBA Macros Seamlessly in WPS Spreadsheets

WPS Office provides robust built-in support for VBA macros, allowing you to run complex data manipulation scripts, including Dictionary mapping, just as you would in Microsoft Excel.

1. Enable Developer Tools: Open your workbook in WPS Spreadsheets and navigate to the Developer tab on the top ribbon.
2. Access the VBA Editor: Click the 'Visual Basic' or 'VBA Editor' button to open the macro environment.
3. Insert Your Code: Right-click on your workbook in the project explorer, select Insert > Module, and paste your header-matching VBA code.
4. Run the Macro: Press F5 or click the Run button to execute the code and automatically copy your data.

High compatibility with Microsoft Excel (.xlsx, .xlsm) formatsBuilt-in VBA editor for writing and debugging macrosLightweight installation with fast loading timesFamiliar interface makes migrating macros completely seamless

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro fail to copy data to a protected worksheet?

If the cells in the destination worksheet are locked and the sheet is protected, VBA cannot modify them and will throw a runtime error. You must explicitly include Worksheet.Unprotect "password" before the copy operation and Worksheet.Protect "password" after it completes.

How do I exclude specific columns like 'Invoice No.' from being trimmed in VBA?

Inside your data processing loop, add a conditional check. For example: If headerName <> "Invoice No." Then targetCell.Value = Trim(sourceCell.Value) Else targetCell.Value = sourceCell.Value End If.

Can I match column headers if they are located on row 3 instead of row 1?

Yes. When building your dictionary in VBA, adjust the row index in the Cells(row, column) property. Change Cells(1, i) to Cells(3, i) so the script reads the headers from the third row.

Why does the Scripting.Dictionary approach work better than traditional copying?

Using a dictionary maps columns by their actual text names rather than their fixed positions (like Column A or B). If a template is updated and a column shifts from position C to E, the dictionary will still find the correct destination automatically.

Excel & Data Problems