VBA & Macro Problems

How to Copy an Excel Online Range Between SharePoint Workbooks

Nimra Malik Oct 9, 2026 869 views

Question details

The user wants to create an Office Script button on a worksheet to copy cell range A3:T41 from a master SharePoint workbook into cell A3 of another workbook.

Copying an Excel Online Range Between SharePoint Workbooks with Office Scripts

Product: Excel Online
Device & OS: not provided
Scenario: Automating data transfer across different SharePoint workbooks using Office Scripts.
Observed behavior: Office Scripts alone cannot directly open or write to external workbooks; cross-workbook data transfer requires integration with Power Automate APIs.

Before you start

Before proceeding, ensure you have active Microsoft 365 permissions to access both the source and destination SharePoint workbooks, and confirm that Power Automate is enabled for your organization's account.

Solution 1Recommended

Use Power Automate to Bridge Office Scripts

Since Office Scripts cannot directly read or write across separate SharePoint workbooks by themselves, you must use Power Automate to retrieve the data from the source and paste it into the destination.

Office Scripts in Excel Online are designed to execute solely within the context of a single workbook. To achieve cross-workbook automation, you need to create two separate Office Scripts (one for reading, one for writing) and link them using a Power Automate flow.

Create a reading script in the master workbook

Open the master workbook in Excel Online, go to the Automate tab, and write a script that returns the values of range A3:T41 as a 2D string or number array.

Create a writing script in the destination workbook

Open the destination workbook, go to the Automate tab, and create a second script that accepts a 2D array as a parameter and sets it starting at cell A3.

Build the Power Automate flow

Log into Power Automate, create a new flow (which can be triggered by a button in Excel), and add the 'Run script' action for the master workbook to extract the data.

Pass data to the destination workbook

Add a second 'Run script' action pointing to the destination workbook, mapping the output array from the first script into the input parameter of the second script.

Use Power Automate to Bridge Office Scripts

Flow Limitations: Complex worksheet structures or dynamic destination behaviors may require advanced Power Automate API configurations. If you face issues, posting the workbook structure in the Microsoft Q&A Office Development section is highly recommended.

Free Microsoft Office alternative

Try WPS Office for Powerful Local Macros and VBA Automation

If you prefer working locally without the complexities of cloud-based Office Scripts and Power Automate flows, WPS Office provides excellent native support for traditional VBA macros. It allows for seamless and direct data transfer across multiple workbooks locally, serving as a lightweight and highly compatible alternative to Microsoft Office.

1. Download and install WPS Office: Get the free version of WPS Office from the official website and install it on your computer.
2. Open your workbooks: Launch WPS Spreadsheets and open your master and destination workbooks in the same application instance.
3. Enable and run VBA macros: Go to the Developer tab, open the VBA Editor, and write a standard macro to copy data directly between the active workbooks.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls) and VBA macros (.xlsm).Execute cross-workbook data transfers easily using traditional desktop VBA without requiring cloud APIs.Lightweight installation with a familiar, easy-to-use tabbed interface.Free to use with comprehensive spreadsheet, document, and presentation capabilities.

Frequently Asked Questions

Can Office Scripts directly open another workbook in SharePoint?

No, Office Scripts cannot natively open, read, or write to external workbooks. They strictly run within the context of the workbook where they are executed. You must utilize Power Automate to pass data between scripts residing in different workbooks.

Can I trigger a Power Automate flow directly from an Excel worksheet button?

Yes, you can use the 'Automate' tab in Excel Online to link a specific Office Script or a Power Automate flow to a button. This allows users to manually initiate the cross-workbook copy process directly from the spreadsheet.

What is the difference between traditional VBA and Office Scripts?

VBA (Visual Basic for Applications) is a desktop-based automation language used in traditional Excel files that can interact with multiple local workbooks simultaneously. Office Scripts are based on TypeScript and are specifically designed for cloud-based automation in Excel on the web, operating strictly within a single workbook boundary.

Excel & Data Problems