Excel Office Scripts: Copy a Column by Header to Another Worksheet
Question details
The user needs an Office Script that finds the column headed Property Name, copies its values from one worksheet, and writes them into the matching column on worksheet P.
- Product
- Microsoft Excel Office Scripts
- Device & OS
- not provided
- Scenario
- A workbook contains a Property Name column whose position may change. The user wants to transfer its values to the corresponding header on another worksheet without copying formatting.
- Observed behavior
- The required column and target range must be identified by header text, and the target values must be overwritten or cleared according to the current data size.
Confirm the source worksheet, the target worksheet name, and the row containing the headers. If the data is in structured Tables, record both table names; otherwise confirm that the header text is exactly Property Name.
Use an Office Script to match Property Name headers and copy values
Use this solution when the source and target columns can move but both sheets contain a consistent header row.
The script below treats the first row of each used range as the header row and writes values only. Change sourceSheetName, targetSheetName, or headerRowIndex when your workbook uses different sheet names or header positions.
In Excel for the web, open the workbook, select the Automate tab, choose New Script, and replace the starter code with the script below.
Use this code: function main(workbook: ExcelScript.Workbook) { const sourceSheetName = "Source"; const targetSheetName = "P"; const headerRowIndex = 0; const source = workbook.getWorksheet(sourceSheetName); const target = workbook.getWorksheet(targetSheetName); if (!source || !target) throw new Error("Check the worksheet names."); const sourceUsed = source.getUsedRange(); const targetUsed = target.getUsedRange(); if (!sourceUsed || !targetUsed) throw new Error("Both worksheets need data."); const sourceValues = sourceUsed.getValues(); const targetValues = targetUsed.getValues(); const sourceHeaders = sourceValues[headerRowIndex].map(value => String(value).trim()); const targetHeaders = targetValues[headerRowIndex].map(value => String(value).trim()); const sourceColumn = sourceHeaders.indexOf("Property Name"); const targetColumn = targetHeaders.indexOf("Property Name"); if (sourceColumn < 0 || targetColumn < 0) throw new Error("Property Name was not found on both sheets."); const copiedValues = sourceValues.slice(headerRowIndex + 1).map(row => [row[sourceColumn]]).filter(row => row[0] !== ""); const oldRowCount = Math.max(targetUsed.getRowCount() - headerRowIndex - 1, 1); target.getRangeByIndexes(headerRowIndex + 1, targetColumn, oldRowCount, 1).clear(ExcelScript.ClearApplyTo.contents); if (copiedValues.length > 0) target.getRangeByIndexes(headerRowIndex + 1, targetColumn, copiedValues.length, 1).setValues(copiedValues); }
Select Save, choose Run, and confirm that worksheet P contains the Property Name values below its matching header. The script clears previous contents in the target data area before writing the new values.
Reference structured Tables instead of worksheet ranges
Use this approach when the Property Name field belongs to named Excel Tables and the table structure is stable.
Move Excel Workbooks to WPS Office for Lightweight Spreadsheet Editing
Office Scripts are a Microsoft Excel automation feature, so WPS Office does not run the script itself. WPS Office provides a free, lightweight spreadsheet alternative with a familiar interface and strong XLSX compatibility, making it easy to open the workbook and copy the Property Name column manually when automation is not required.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, select Open, and load the Excel XLSX workbook.
- 2. Locate the matching headers: Use the sheet tabs to open the source sheet and worksheet P, then use Find from the Home tab to locate Property Name.
- 3. Copy values to the target column: Select the source data below Property Name, choose Copy, select the matching target cells on worksheet P, and use Paste Special or Values Only when available.
Frequently Asked Questions
How does the Office Script find the Property Name column?
It reads the header row from each worksheet, trims the header text, and uses indexOf("Property Name") to find the source and target column positions.
Can the script copy only values without formatting?
Yes. The script reads cell values with getValues() and writes them with setValues(), so formatting, formulas, comments, and column widths are not copied.
What should I change if the headers are not in the first row?
Update headerRowIndex in the script. The value is zero-based, so use 1 for the second row, 2 for the third row, and so on.
How can I prevent old target values from remaining after a shorter run?
The script clears the existing target data range with target.getRangeByIndexes(...).clear(ExcelScript.ClearApplyTo.contents) before writing the new values.
Can this work with structured Excel Tables?
Yes. Use sourceTable.getColumnByName("Property Name") and targetTable.getColumnByName("Property Name") to reference Table columns directly, then read and write their data-body ranges.
