Macro not working

Question

Hello Gurus,

I can't get a Macro to work. Trying to create a macro for Paste Only Values.

I tried to record a Macro with keystrokes ALT, then E, then S, then V, then ENTER.

When I use these keystrokes outside of the Macro recording, they perform correctly to past-values. But when I use them while recording, it enters V in the cell, instead of pasting. Could not find any way to get this to record.

Alternatively, I tried to use WIN+V, then selecting the Paste Values option. I can successfully record this as a Macro. Then immediately after recording, I can successfully use the Macro. But after I close Excel and open again, the Macro no long works correctly. It then does a paste-regular, instead of paste-values. (Eg, it pastes a formula instead of the resulting value.

Does anyone have any idea why neither of these work, and how to make either of them work? Or any other method to create a Macro with this effect?

Thanks!

-John

Answer 1

At first you need a PERSONAL.XLSB which is loaded by Excel at startup automatically.

Create and save all your macros in a single workbook - Office Support

Copy the code below into that file into a regular module.

Close the VBA editor and press ALT+F8 in Excel

You should now see the macro inside your personal workbook, assign CTRL+SHIFT+V to the macro. There are also other ways shown in this article:

Run a macro - Office Support

Close Excel and save the PERSONAL.XLSB file

That's it.

Andreas.

Sub PasteValues()

ActiveCell.PasteSpecial xlPasteValues
End Sub

Answer 2

Re: "any other method to create a Macro with this effect"

Use Ctrl + V to paste values.

It is also much faster than trying to get a macro to run.

The problem with using a macro is that the "copy" portion is lost

unless the paste follows immediately.

That applies to all VBA code you might create later on.

'---

Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)