VBA & Macro Problems

How to Convert a VBA Copy and Paste Macro to an Office Script in Excel

Nimra Malik Oct 9, 2026 871 views

Question details

The user needs to translate a legacy VBA macro that copies a specific cell range and pastes it as values into another worksheet into a modern Office Script.

How to Convert a VBA Copy and Paste Macro to an Office Script

Product: Microsoft Excel
Device & OS: not provided
Scenario: Migrating a desktop VBA automation workflow to Office Scripts for web and cloud compatibility.
Observed behavior: The user is looking for the Office Scripts equivalent of the VBA Range.Copy method and xlPasteValues operation to transfer data between sheets.

Before you start

Ensure you are using a Microsoft 365 subscription with access to Excel on the web or a compatible desktop version, as Office Scripts are a cloud-based automation tool and function differently from desktop-bound VBA macros.

Solution 1Recommended

Use getValues() and setValues() in Office Scripts

In Office Scripts, there is no direct equivalent to VBA's Paste Special command. Instead, you extract the values from the source range as an array and apply them directly to a target range of the same size.

To mimic the VBA xlPasteValues behavior, you will use the getValues() method on the source worksheet range and the setValues() method on the target worksheet range.

Keep in mind that the dimensions of the target range must perfectly match the dimensions of the source range's array, otherwise the script will throw an error.

Open the Automate Tab

Open your workbook in Excel for the web or Excel 365 for desktop. Navigate to the Automate tab on the ribbon and click on 'New Script' to open the Code Editor.

Define Worksheets

Inside the main function block, assign your source and target worksheets to variables using workbook.getWorksheet(). For example: const wsSource = workbook.getWorksheet('Writing'); and const wsTarget = workbook.getWorksheet('AfCL-L&N Precursors');

Extract Source Values

Retrieve the data from the source range by chaining getRange() and getValues(). For example: const sourceValues = wsSource.getRange('F6:Q55').getValues();

Set Target Values

Write the extracted array to the destination sheet by chaining getRange() and setValues(). Ensure the target range size matches. For example: wsTarget.getRange('F195:Q244').setValues(sourceValues);

Save and Run

Click 'Save script' at the top of the Code Editor, give your script a name, and press 'Run' to execute the copy and paste operation.

Use getValues() and setValues() in Office Scripts

Code Structure: Your complete script should look like this: function main(workbook: ExcelScript.Workbook) { const wsSource = workbook.getWorksheet('Writing'); const wsTarget = workbook.getWorksheet('AfCL-L&N Precursors'); const sourceValues = wsSource.getRange('F6:Q55').getValues(); wsTarget.getRange('F195:Q244').setValues(sourceValues); }

Free Microsoft Office alternative

Keep Using Your Existing VBA Macros with WPS Office

If converting your complex VBA macros to Office Scripts feels overwhelming or unnecessary, you can continue using standard VBA code seamlessly. WPS Office provides native desktop support for VBA macros without requiring cloud migrations or code rewrites.

1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your computer.
2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheets and open your existing .xlsm or .xlsb file containing the original VBA macros.
3. Enable Macros and Run: Accept the security prompt to enable macros. Your original copy-and-paste VBA macros will run perfectly without needing any conversion.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .xlsm)Native support for existing VBA macros—no need to rewrite as Office ScriptsLightweight, fast, and free to downloadFamiliar user interface with zero learning curve

Frequently Asked Questions

Why should I convert VBA to Office Scripts?

Office Scripts are designed to work seamlessly in Excel on the web and integrate with cloud automation tools like Power Automate. VBA, on the other hand, is restricted to desktop versions of Excel and cannot be triggered via cloud flows.

Is there a direct 'Paste Special' method in Office Scripts?

No, Office Scripts handle data transfer differently than VBA's clipboard actions. Instead of a single Paste Special command, you use getValues(), getFormulas(), or getFormat() to pull specific attributes, and setValues(), setFormulas(), or setFormat() to apply them.

Will my Office Script work if the target range is a different size?

No. When using setValues(sourceValues), the dimensions of the target range must exactly match the rows and columns of the two-dimensional array extracted from the source range. Otherwise, the script will throw a size mismatch error.

Excel & Data Problems