Saturday, December 4, 2010

Mac Excel 2011 Paste Special Bug Work-around

Macros are back in Microsoft Excel:Mac 2011
As mentioned in a previous post, the paste-special shortcut does not work in Excel:Mac 2011, but it is easy to write a work-around since macro recording is back (!) in this version of Office.

I have not been able to make a shortcut to open the Paste Shortcut menu, but one can address each of the individual options with an individual macro.  Post a comment if you figure out how.

Here is a macro for paste-values, and it is about the simplest thing in the world.  It is useful for when you want to save the result of a calculation, without moving the formula itself. 

Sub PasteSpecialValues()
'
' PasteSpecialValues Macro
' Paste SpecialValues
'
' Keyboard Shortcut: Option+Cmd+v
'
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

The Paste Format one is similar.

Sub PasteSpecialValues()
Sub PasteFormats()
'
' PasteFormats Macro
'
' Keyboard Shortcut: Option+Cmd+Shift+F
'
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

I could build a little menu in Visual Basic to similar the real Paste-Special function. It would be better if Microsoft would fix the bug, so that Shift-Command -V would operate Paste-Special, as the menu indicates.