Data Import & Export

Send Excel Cell Values to Another Workbook and Clear the Source

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

Transfer values from a defined Excel range to another workbook and clear the source cells after the transfer succeeds.

Send Excel Cell Values to Another Workbook and Clear the Source
Product
Microsoft Excel, Power Automate, and Office Scripts
Device & OS
not provided
Scenario
Users submit values from a range such as C7:C18 to a destination workbook.
Observed behavior
The desired result is for the destination workbook to receive the values while the source range is cleared only after a successful transfer.
Before you start

Store both workbooks in OneDrive for Business or SharePoint and confirm that your account can use the Excel Online (Business) connector and Office Scripts. Make sure the destination workbook has a table with 12 columns if you want to store C7:C18 as one row.

Solution 1Recommended

Transfer the range with Power Automate and clear it with Office Scripts

Use this approach for cloud-based workbooks when each submission should be written to a destination table before the source cells are cleared.

The example reads C7:C18 from a worksheet named Input and writes the 12 values into one row of a destination table. Adjust the worksheet name, range, and destination columns to match your files.

Prevent users from editing or resubmitting the source range while the flow is running. If the destination write succeeds but the clear step fails, check the destination before retrying to avoid a duplicate row.

1
Prepare the destination table

Open the destination workbook in Excel, enter 12 column headers, select them, and choose Insert > Table. Save the workbook in OneDrive for Business or SharePoint.

2
Create a script to read the values

Open the source workbook in Excel for the web and choose Automate > New Script. Save a script named ReadInput with: function main(workbook: ExcelScript.Workbook): (string | number | boolean)[] { return workbook.getWorksheet("Input").getRange("C7:C18").getValues().map(row => row[0]); }

3
Create a script to clear the range

In Automate > New Script, save a second script named ClearInput with: function main(workbook: ExcelScript.Workbook) { workbook.getWorksheet("Input").getRange("C7:C18").clear(ExcelScript.ClearApplyTo.contents); }

4
Build the transfer flow

In Power Automate, choose Create > Instant cloud flow and select Manually trigger a flow. Add Excel Online (Business) > Run script to run ReadInput on the source workbook, then add Add a row into a table for the destination workbook. Map the 12 returned values to the corresponding table columns.

5
Validate and clear after the write

Add a Condition before the destination action to check that the required values are present. After Add a row into a table, add another Run script action for ClearInput and use Configure run after so it runs only when the destination action succeeds. Test the flow with sample values and check both workbooks.

Transfer the range with Power Automate and clear it with Office Scripts
Handle failed runs: Review the flow's run history for failed transfers or clear operations. Do not clear the source after a failed destination write; investigate any successful write followed by a failed clear before submitting again.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and compatibility with Excel formats for an easy transition. The Power Automate and Office Scripts flow described above is a Microsoft service workflow and is not fixed or replaced by WPS Office.

Open and edit Excel XLSX and XLS filesReview and update source and destination spreadsheet dataUse a familiar spreadsheet interfaceWork with a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why does the destination workbook need a table?

The Excel Online (Business) action Add a row into a table writes to an Excel table. In the destination workbook, select the headers and choose Insert > Table before configuring the action.

Will clearing C7:C18 remove its formatting?

The example ClearInput script uses ExcelScript.ClearApplyTo.contents, which clears cell contents while leaving formatting in place.

What happens if the destination write fails?

Configure the ClearInput Run script action to run only after the destination action succeeds. If the write fails, the source values remain available for investigation and retry.

Can I use Microsoft Forms instead of an Excel input range?

Yes. In Power Automate, start with a Microsoft Forms submission trigger and use the response details to populate a row in the destination Excel table. A Forms response does not require clearing an Excel source range.

Can I use a different source range or worksheet?

Yes. In both Office Scripts, replace Input and C7:C18 with your worksheet name and range. Update the destination table and flow mappings to match the number of values.