VBA & Macro Problems

How to Create an Excel VBA Macro to Paste Values and Source Formatting

Bushra Parveen Oct 12, 2026 869 views

Question details

The user needs to create a VBA macro to paste copied data as values while keeping the source formatting, operating on the currently selected cell instead of a fixed range.

How to Create an Excel VBA Macro to Paste Values and Source Formatting

Product: Excel
Device & OS: macOS
Scenario: The user wants to automate pasting data as values with original formatting anywhere on the spreadsheet without being restricted to a hard-coded destination.
Observed behavior: Standard recorded macros often hard-code the destination range, which prevents the macro from working dynamically on whichever cell the user currently selects.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that you have already copied the source data to your clipboard before running the macro.

Solution 1Recommended

Use Selection.PasteSpecial in VBA

Write a VBA script that applies 'PasteSpecial' twice on the active selection—first for values and number formats, then for general source formatting.

When you record a macro, Excel often hard-codes specific cell references (like Range("C8").Select). By modifying the VBA code to use the 'Selection' object, the macro will dynamically apply to whichever cell you currently have selected.

Open the VBA Editor

Open Excel and press 'Option + F11' on Mac (or 'Alt + F11' on Windows) to launch the VBA Editor.

Insert a New Module

Click 'Insert' from the top menu and select 'Module' to create a blank workspace for your code.

Enter the VBA Code

Type the following macro: Sub PasteValuesAndSourceFormats() then add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats followed on the next line by Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False and finally close with End Sub.

Remove Hard-Coded Ranges

Ensure there are no hard-coded range selections like Range("C8").Select in your code so the destination is not fixed.

Run the Macro

Return to your worksheet, select your desired destination cell, and run the macro via the 'Developer' tab > 'Macros'.

Dynamic Pasting: This macro will now work on any cell you select, pasting only the values and the exact formatting of your previously copied data.

Advanced Automation

Use Macros Seamlessly in WPS Spreadsheets

WPS Office provides excellent compatibility with Excel macros and VBA, allowing you to automate repetitive tasks like pasting values and formatting with ease.

1. Enable Macros in WPS: Open WPS Spreadsheets, go to the 'Developer' tab, and click 'Macros' to access the VBA environment.
2. Insert the VBA Code: Create a new macro and paste the Selection.PasteSpecial script exactly as you would in Microsoft Excel.
3. Run and Automate: Copy your data, select a new destination cell, and run the macro to instantly paste values and source formats.

Fully compatible with Microsoft Excel macro (.xlsm) formatsSupports standard VBA syntax including Selection.PasteSpecialLightweight application with a highly familiar user interfaceFree to download and easy to set up for daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my recorded macro always paste into the same cell?

When recording a macro, Excel captures your exact clicks, including selecting a specific cell. To fix this, manually edit the VBA code and replace the specific range reference with the 'Selection' object.

Will this macro overwrite existing formulas in the destination cell?

Yes, because the macro explicitly pastes the copied data as 'Values', any existing formula in the destination cell will be replaced by the static value.

Can I assign a keyboard shortcut to this VBA macro?

Yes. Go to the Developer tab, click 'Macros', select your new macro, click 'Options', and assign a convenient shortcut key for faster execution.

What happens if I run the macro without copying anything first?

The macro will return a run-time error because the clipboard is empty or does not contain valid range data for the PasteSpecial method. Always copy your source data before executing the macro.

Excel & Data Problems