Excel Office Script to Find the Last Used Row and Copy a Formula
Question details
The user needs an Excel for the web Office Script that identifies the last used row on the active worksheet and copies that row's formulas to the next row.
- Product
- Microsoft Excel for the web
- Device & OS
- not provided
- Scenario
- The user is automating a worksheet update and wants each new row to inherit formulas from the previous row without manual copying.
- Observed behavior
- Excel for the web does not run VBA, so the script must use Office Scripts to find the used range and copy the last used row down one row.
Open the workbook in Excel for the web and confirm you have editing access. Make sure the Automate tab is visible so you can create and run the Office Script.
Use an Office Script to Copy the Last Used Row to the Next Row
Use this solution when you need an Excel for the web script that finds the last used row and copies its formulas into the next blank row.
Office Scripts run in Excel for the web and are stored with the workbook. The script uses getUsedRange() to locate the last cell in the used range, gets the entire used row, and then copies that row to the row immediately below.
In Excel for the web, open the workbook, select the Automate tab, click New Script, and replace the default code with the Office Script shown in the next step.
Paste this script: function main(workbook: ExcelScript.Workbook) { const sheet = workbook.getWorksheet('Sheet1'); const usedRange = sheet.getUsedRange(); const lastRow = usedRange.getLastCell().getEntireRow().getUsedRange(); lastRow.getOffsetRange(1, 0).copyFrom(lastRow); } Change 'Sheet1' to your exact worksheet name.
Click Run in the Code Editor, then review the row immediately after the last used row to confirm that the formulas were copied correctly.
Use WPS Office Spreadsheets as a Free Excel Alternative for Macro Automation
WPS Office is a free, lightweight Microsoft Office alternative that opens and saves Excel workbooks with high compatibility. Its familiar spreadsheet interface, built-in macro support, and flexible formula handling make it easy to migrate recurring formula-row workflows without a Microsoft 365 subscription.
Frequently Asked Questions
What should I do if the Automate tab is missing in Excel for the web?
The Automate tab can require a Microsoft 365 work or school plan with Office Scripts enabled. If it is missing, check your license or ask your administrator to enable Office Scripts.
Can I use this script on a worksheet with a different name?
Yes. Change getWorksheet('Sheet1') to match the exact worksheet name in your workbook before running the script.
Does copyFrom copy formulas or only the cell values?
copyFrom copies the entire row content, including formulas, formatting, and values, so the next row inherits the formulas as expected.
Can I run this Office Script in WPS Office?
WPS Office does not run Excel Office Scripts, but WPS Spreadsheets includes macro and formula tools you can use for similar desktop automation.
Why should I test the script on a copy of the workbook?
Office Scripts make changes immediately. Testing on a copy prevents accidental overwrites or extra copied rows in production data.
