Convert Excel VBA Worksheet Change Automation to Office Scripts for Archive Rows
Question details
Convert a VBA Worksheet_Change workflow so rows are archived when Yes is selected in column N of an Excel workbook stored in SharePoint.

- Product
- Microsoft Excel for the web Office Scripts
- Device & OS
- not provided
- Scenario
- The user wants to copy a source row to the Historical Referrals sheet and remove it from the original sheet after its status is changed to Yes through a drop-down list.
- Observed behavior
- Office Scripts can perform the row-copying and deletion logic, but they cannot directly use VBA Worksheet_Change event handlers.
Make sure the source and Historical Referrals sheets have compatible column layouts, and confirm that column N contains the Yes status value used by the script. Decide how the script will run, because Office Scripts do not automatically fire when a worksheet cell changes.
Create an Office Script that archives rows marked Yes
Use this method when you need to replace the VBA row-archiving logic in Excel for the web or Microsoft Teams.
Office Scripts use TypeScript rather than VBA. The script should read the source range or table, identify rows where column N contains Yes, copy those rows to Historical Referrals, and then delete the matching source rows from bottom to top so row positions remain accurate.
Open the workbook in Excel for the web, select the Automate tab, and choose New Script.
In the script, use workbook.getWorksheet("Source Sheet Name") for the active referral sheet and workbook.getWorksheet("Historical Referrals") for the destination sheet. Replace Source Sheet Name with the actual worksheet name.
Use getUsedRange() or getTables() to read the source data. Check the value in column N, which is index 13 when counting from zero, and collect rows whose normalized value equals "YES".
Get the next available row on Historical Referrals with getUsedRange() and use setValues() to write each matching source row to the archive sheet.
Sort the collected source row indexes in descending order, then call getRangeByIndexes() or getCell().getEntireRow().delete(ExcelScript.DeleteShiftDirection.up) for each row. Deleting from the bottom prevents later row indexes from shifting.
Select Automate > Run to execute the script, then confirm that each Yes row appears on Historical Referrals and is no longer present on the source sheet.

Run the archive script through Power Automate
Use this method when the archive check must run on a schedule or after a supported workbook-related workflow instead of being started manually.
Use WPS Spreadsheets for familiar desktop VBA-style worksheet automation
If your workflow depends on desktop spreadsheet automation and familiar macro-oriented editing, WPS Office provides a free, lightweight alternative with a familiar spreadsheet interface. It supports seamless work with common Excel files, helping teams migrate documents while maintaining everyday spreadsheet productivity.

Frequently Asked Questions
Can Office Scripts use the VBA Worksheet_Change event?
No. Office Scripts do not support VBA event procedures such as Worksheet_Change. Run the script from Excel for the web or call it through a supported Power Automate workflow.
Which column index represents column N in an Office Script?
Column N is the 14th worksheet column. In zero-based Office Scripts indexes, it is index 13 when you read row values from a range or table.
Why should source rows be deleted from bottom to top?
Deleting a row changes the indexes of every row below it. Processing matched rows in descending order prevents the script from deleting or skipping the wrong records.
Can the archive sheet have a different layout from the source sheet?
Yes, but the script must map source values to the correct destination columns before calling setValues(). If both sheets use the same layout, the script can copy the complete row array directly.
How can I test an Office Script before using it in Power Automate?
Open the workbook in Excel for the web, select Automate, choose the saved script, and select Run. Test with a small set of rows whose column N status is Yes, then verify both worksheets before adding the flow.




