VBA & Macro Problems

Convert Excel VBA Worksheet Change Automation to Office Scripts for Archive Rows

Emma BrownEmma Brown Nov 12, 2026 869 views

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.

Convert Excel VBA Worksheet Change Automation to Office Scripts for Archive Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the Office Scripts editor

Open the workbook in Excel for the web, select the Automate tab, and choose New Script.

2
Define the source and archive worksheets

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.

3
Read and identify status rows

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

4
Append matching rows to the archive sheet

Get the next available row on Historical Referrals with getUsedRange() and use setValues() to write each matching source row to the archive sheet.

5
Delete archived source rows safely

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.

6
Run and verify the script

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.

Create an Office Script that archives rows marked Yes
Worksheet events are not available in Office Scripts: An Office Script cannot directly replace VBA Worksheet_Change. It must be started manually or invoked by a supported Power Automate workflow.
Free Microsoft Office alternative

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.

Lightweight spreadsheet workspace for managing referral and archive worksheetsFamiliar Excel-style interface for formulas, tables, and worksheet operationsCompatible with Microsoft Excel .xlsx, .xls, and .xlsm file formatsFree WPS Office tools support an easy transition from Microsoft Office workflows
microsoft office alternative - wps office

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.