How to Use VBA in WPS Spreadsheets to Paste Values While Keeping Colors and Borders
Question details
User needs a VBA macro to replace formula results with static values while retaining existing cell colors, borders, and other formatting in WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Converting a formula-based table to displayed results for sharing, reporting, or archiving without losing visual formatting.
- Observed behavior
- A standard value-only paste returns the displayed results but can drop or change cell colors and borders, requiring a second formatting paste.
Open the target workbook in WPS Spreadsheets, press Alt+F11 to open the VBA editor, and identify the exact source and destination ranges. Work in a macro-enabled workbook format such as .xlsm if you need to save the VBA project.
Use Two VBA PasteSpecial Operations to Paste Values and Then Formats
Recommended when you want an automated, repeatable macro that replaces formulas with their current results and reapplies the original colors and borders.
The macro copies the source range, pastes only values to the destination, copies the source again, and pastes only formats. This two-step approach preserves fill colors, font colors, borders, number formats, and other cell formatting that a value-only paste would not carry over.
Example VBA code: Sub PasteValuesKeepFormatting() Dim sourceRange As Range Dim destRange As Range Set sourceRange = Range("A1:D10") Set destRange = Range("F1") sourceRange.Copy destRange.PasteSpecial Paste:=xlPasteValues sourceRange.Copy destRange.PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False End Sub
In WPS Spreadsheets, press Alt+F11 or click the Developer tab and select Visual Basic to open the VBA editor.
In the Project window, right-click the current workbook or VBAProject, choose Insert, and then click Module to create a new code module.
Paste the VBA code into the module and replace A1:D10 with your source range and F1 with the top-left cell of your destination range.
Place the cursor inside the macro and press F5, or click Run > Run Sub/UserForm. The macro pastes values first and formatting second in one pass.
Use Paste Special Twice from the WPS Spreadsheets Ribbon
Use this manual method when you only need to convert one range and do not need to save a VBA macro.
Run a WPS Spreadsheets VBA Macro to Paste Values and Keep Colors and Borders
WPS Spreadsheets can directly automate this task with a short VBA macro. The two-step PasteSpecial method converts formulas to their current values and then reapplies the original colors and borders, so you keep the exact table appearance without manual reformatting.
- 1. Open WPS Spreadsheets: Launch WPS Office, open the workbook containing the formula-based table, and go to the sheet you want to change.
- 2. Open the VBA editor: Press Alt+F11, or click the Developer tab and select Visual Basic.
- 3. Insert and customize the module: Insert a new module, paste the PasteSpecial macro, and set the source and destination ranges to match your table.
- 4. Run the macro: Press F5 or click Run > Run Sub/UserForm to paste values first and formatting second.
Frequently Asked Questions
Does xlPasteFormats restore borders and fill colors only, or also number formats?
xlPasteFormats pastes cell formatting including fill colors, font colors, borders, number formats, alignment, and other visual formats. It does not paste column widths unless you add xlPasteColumnWidths separately.
Can I paste values and formatting to a different worksheet?
Yes. Set the source range to the original sheet’s range and set the destination range to a range on another worksheet, for example Worksheets("Sheet2").Range("F1").
Will this VBA code work in Microsoft Excel and WPS Spreadsheets?
The xlPasteValues and xlPasteFormats constants are supported by Excel VBA and WPS Spreadsheets macro editing, so the same code works in both applications for most standard workbook scenarios.
How do I add this code to an existing VBA project?
Open the VBA editor with Alt+F11, expand the workbook project in the Project window, right-click Modules, choose Insert > Module, and paste the code into the new module.
