How to Fix VBA PasteSpecial Expected End of Statement Error
Question details
The user is encountering an 'Expected: end of statement' error when trying to run a VBA macro that copies and pastes data.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Writing or executing a VBA script to copy a range of cells and paste them as values or with special formatting.
- Observed behavior
- The VBA code compiler throws an 'Expected: end of statement' error because Range.Copy and PasteSpecial are written together as a single chained expression.
Before modifying your VBA code, ensure you save a backup of your current workbook. Open the VBA Editor (ALT + F11) to locate the exact line of code causing the syntax error.
Separate the Copy and PasteSpecial Statements
The most effective way to fix this error is to split the chained Copy and PasteSpecial methods into two distinct lines of code.
In VBA, while the Range.Copy method can accept a direct destination argument for a standard paste, PasteSpecial is a distinct method that must be applied to a target Range object on its own line. Attempting to chain them together (e.g., Range.Copy.PasteSpecial) breaks the expected syntax rules of VBA and triggers the compiler error.
Open the Visual Basic Editor and find the line highlighted in red or yellow that contains both the Copy and PasteSpecial commands.
Remove the chained PasteSpecial method. Leave only the copy instruction on the first line, formatted as: Rng.Copy (where 'Rng' is your source range).
On the immediate next line, specify your target range and append the PasteSpecial method. For example: Rng.Offset(-1, 0).PasteSpecial xlPasteValues.
Save your code and run the macro again to ensure the data is copied and pasted without triggering the expected end of statement error.
Run Macros Seamlessly with WPS Spreadsheet
WPS Spreadsheet provides a highly compatible and familiar environment for handling advanced data tasks, running macros, and processing scripts. Editing VBA code to fix syntax errors is straightforward and intuitive.
- 1. Open your workbook: Launch WPS Spreadsheet and open your .xlsm file containing the macro.
- 2. Access developer tools: Navigate to the 'Developer' tab on the top ribbon menu.
- 3. Open the VBA Editor: Click on 'Macros' or 'Visual Basic' to launch the built-in code editor.
- 4. Edit and run: Separate the Copy and PasteSpecial lines in your script, then execute the macro seamlessly.

Frequently Asked Questions
Why doesn't standard Range.Copy throw this end of statement error?
The standard 'Range.Copy' method natively accepts a single argument for the destination range on the same line (e.g., Range("A1").Copy Range("B1")). However, 'PasteSpecial' provides specific pasting parameters like values or formatting, which VBA requires to be executed as a separate method call on a new line.
What is the correct syntax for pasting values only in VBA?
First, copy the source range using 'SourceRange.Copy'. Then, on a completely new line, type 'TargetRange.PasteSpecial Paste:=xlPasteValues' to paste only the cell values without transferring any background colors, borders, or font styles.
Does WPS Spreadsheet support executing VBA macros?
Yes, standard macro execution is supported in compatible versions of WPS Office. Ensure you are using the edition that includes the VBA module to seamlessly edit and troubleshoot your .xlsm files.
How do I clear the clipboard in my macro after using PasteSpecial?
To free up system memory and remove the selection highlight from copied cells, add the statement 'Application.CutCopyMode = False' immediately after your 'PasteSpecial' line in the macro code.




