How to Copy Excel Formatting, Formulas, and Validation with Office Scripts
Question details
The user wants to copy complete Excel table rows, including formatting, formulas, data validation, and values, using Office Scripts rather than just pasting values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Moving or copying entire table rows in Excel while preserving all cell properties via automation.
- Observed behavior
- Instead of copying only values, the user needs an automated script to retain formulas, formatting, data validation, and current values during the transfer.
Ensure you have an active Microsoft 365 subscription with access to the Automate tab in Excel on the web or desktop to use Office Scripts.
Record Actions and Use Range.copyFrom in Office Scripts
The most efficient way to capture all cell properties is by recording your actions and modifying the generated script to use the Range.copyFrom method with ExcelScript.RangeCopyType.all.
Office Scripts provides a powerful way to automate repetitive tasks in Excel. While extracting values using basic arrays is common, duplicating a range perfectly requires specific API methods.
By utilizing the Range.copyFrom method, you instruct Excel to replicate not just the data, but the underlying formulas, rich formatting, and any strict data validation rules applied to those cells.
Launch your Excel workbook and navigate to the 'Automate' tab on the main ribbon.
Click on 'Record Actions'. Manually highlight the source row or range, press Ctrl+C to copy, select your destination cell, and paste it. Click 'Stop' to finalize the recording.
Click 'Edit' on the newly created script in the Code Editor pane to view the generated TypeScript code.
Locate the paste action in the code. Ensure the destination range calls the copyFrom method like this: destinationRange.copyFrom(sourceRange, ExcelScript.RangeCopyType.all);. This guarantees formulas, formatting, and validation are included.
Save your script. You can now click 'Run' anytime to copy the complete properties of your selected rows automatically.
Need a Powerful Yet Free Spreadsheet Tool? Try WPS Office
If you are dealing with complex Excel macros, VBA, or scripting challenges, WPS Office provides a robust, lightweight, and completely free alternative with high compatibility for Microsoft Excel files and familiar developer tools.
- 1. Download and Install WPS Office: Visit the official WPS Office website to download the free suite and follow the quick installation prompts.
- 2. Open Your Excel Workbook: Launch WPS Spreadsheets and open your existing .xlsx or .xlsm file without worrying about format conversion.
- 3. Utilize Advanced Macros: Access the Developer tab to explore WPS Office's built-in Macro capabilities for automating your table row copying tasks.
Frequently Asked Questions
What does ExcelScript.RangeCopyType.all include?
It includes the cell values, formulas, number formatting, cell formatting (like colors and borders), and data validation rules from the source range.
Can I copy only the values using Office Scripts?
Yes, you can modify the script to use ExcelScript.RangeCopyType.values instead. This will paste only the text or numbers, dropping any underlying formulas, colors, or validation rules.
Where can I find the Automate tab in Excel?
The Automate tab is located on the main ribbon in Excel on the web, and in Excel for Windows or Mac. Note that it is generally available for users with an eligible Microsoft 365 commercial or educational license.
Why is my data validation not copying over when I run my script?
If you are using basic array methods like range.setValues(), Excel only transfers the raw data strings. You must specifically use the .copyFrom() method with the RangeCopyType.all parameter to copy validation rules.
