VBA & Macro Problems

How to Find the Next Empty Excel Cell and Paste Values Using Office Scripts

Nimra Malik Oct 10, 2026 869 views

Question details

The user needs an Office Script to copy selected data and paste it into the next available empty cell in a row on a different worksheet.

Office Script to Find the Next Empty Excel Cell and Paste Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating data transfer between worksheets in a SharePoint-stored Excel workbook by copying a selection and appending it to the end of a row without overwriting existing data.
Observed behavior: The goal is to successfully identify the destination worksheet, locate the next blank position dynamically, and paste the values.

Before you start

Ensure your Excel workbook is saved in SharePoint or OneDrive, as Office Scripts are primarily supported in the web version of Microsoft 365 and require cloud storage to function properly.

Solution 1Recommended

Create an Office Script to Copy and Paste to the Next Empty Cell

Write a custom Office Script to automate reading your selected range, finding the last used cell in your destination sheet, and pasting the data into the adjacent empty cell.

Office Scripts use TypeScript to interact with Excel on the web. By utilizing the copyFrom method, you can easily replicate VBA-like copy and paste behaviors for your cloud-based workbooks.

Open the Automate Tab

Open your Excel workbook in the browser or desktop app, navigate to the 'Automate' tab on the ribbon, and click on 'New Script' to open the Code Editor.

Define the Source Range

In your script, define the data you want to copy by getting the user's current selection. Use code like: let selectedRange = workbook.getSelectedRange();

Identify the Destination Worksheet

Specify the worksheet where the data will be pasted. Assign this to a variable: let destSheet = workbook.getWorksheet("YourDestinationSheetName");

Locate the Next Empty Cell

Use script logic to find the last used cell in your target row (e.g., using getRangeEdge) and offset it by one column to identify the empty target cell.

Execute the Copy Operation

Apply the copyFrom method on your destination cell, passing the source range. For example: targetCell.copyFrom(selectedRange, ExcelScript.RangeCopyType.values);

Save and Run

Rename your script for easy identification, save it, and click 'Run' to test the automation. Verify that the data is appended to the correct row without overwriting.

Create an Office Script to Copy and Paste to the Next Empty Cell

Customize Your Script: Make sure to replace placeholder worksheet names and starting cells in your code with the actual names and ranges used in your specific workbook.

Free Microsoft Office alternative

Automate Your Spreadsheets Seamlessly with WPS Office

While Office Scripts are exclusive to Microsoft 365 cloud environments, WPS Office provides a powerful, desktop-friendly Macro and VBA environment for automating repetitive tasks. Enjoy robust spreadsheet automation completely free, without relying on cloud storage.

Fully compatible with Microsoft Excel (.xlsx, .xls) and macro-enabled formats (.xlsm).Robust built-in VBA and JS Macro support for complex spreadsheet automation.Lightweight installation and blazing-fast performance on any desktop device.Free to use with a familiar, easy-to-navigate tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use this Office Script on the desktop version of Excel?

Office Scripts are primarily designed for Excel on the web, stored in OneDrive or SharePoint. However, if you have an eligible Microsoft 365 subscription, you can run them on the desktop versions of Excel for Windows and Mac.

How do I find the last used column in a row using Office Scripts?

You can find the last used column by utilizing the getRangeEdge method. For example, worksheet.getRange("A1").getRangeEdge(ExcelScript.KeyboardDirection.right) helps identify where the current data ends.

Why is the copyFrom method overwriting my existing data?

This happens if the destination cell is not correctly offset from the last used cell. Ensure your script calculates the next empty column index (last used column + 1) before calling copyFrom.

Can I automate this script to run automatically without clicking a button?

Yes, you can use Power Automate to trigger your Office Script based on specific events, such as when a new item is added to a SharePoint list or at a scheduled time every day.

Excel & Data Problems