Create an undoable 'paste formula' macro

[Luc]

Hello,
I use the 'Paste Special Formula' functionality very often
and therefore would like to set up a shortcut key. I have
recorded a macro but the BIG issue is that then, I am not
able to undo if I pasted in the wrong place.
I did found a few exemples of 'Undo' VBA macros on the NEt
but none would work with 'Paste Special' as the content of
the clipboard gets cleared when I launch them (and
therefore there is nothing to copy anymore!)
Would anyone be kind enough to provide me with the
complete code for those two macros (Paste Formula and Undo
paste formula)?

Many thanks

 

[Jan Karel Pieterse]

Hi Luc,

I use the 'Paste Special Formula' functionality very often
and therefore would like to set up a shortcut key. I have
recorded a macro but the BIG issue is that then, I am not
able to undo if I pasted in the wrong place.
I did found a few exemples of 'Undo' VBA macros on the NEt
but none would work with 'Paste Special' as the content of
the clipboard gets cleared when I launch them (and
therefore there is nothing to copy anymore!)
Would anyone be kind enough to provide me with the
complete code for those two macros (Paste Formula and Undo
paste formula)?

This clumsy code does the paste formula thing without you loosing your
undo:

Sub PasteFormula()
SendKeys "%es{down}~"
End Sub

You must attach the macro to a toolbar button for it to work though and
it only works on an English Excel.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Luc]

VERY clever indeed! Thanks a lot.

-----Original Message-----
Hi Luc,


This clumsy code does the paste formula thing without you loosing your
undo:

Sub PasteFormula()
SendKeys "%es{down}~"
End Sub

You must attach the macro to a toolbar button for it to work though and
it only works on an English Excel.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

.