Copy Formula Contents Only

[jolyeu]

(Bumping this concept from another user who was unable to answer their question in 2016)

Sometimes, it is necessary to copy the formula contents of a cell in order to change the position of the formula on the sheet (formatting, formula building, etc.).

To do this, one must open the cell in edit mode, copy the contents, and then go back to navigation mode. The combo is: F2 > Ctrl-A > Ctrl-C > Esc

Seems like it should be simple enough to automate. Au contraire!

When you try to record this macro, Excel registers no code - no excel functions were performed.

Is there any Excel workaround? Or am I just going to have to find a windows keystroke shortcut recorder.

Thanks

 

[Artik]

1. In the VBA editor, choose Tools/References from the menu and check the 'Microsoft Forms 2.0 Object Library' in the list.
2. In a standard module, such as Module1, insert the below procedure.
3. In Excel, press Alt+F8 keyboard shortcut, select CopyFormula in the list and press [Options...] button. In the 'Shortcut key' field, insert the uppercase letter Z and press [OK]. You can exit the 'Macro' window by pressing the [Cancel] button.
4. Select the cell with the formula to copy and press the defined shortcut. Then select the target cell and use Ctrl+v shortcut.

Sub CopyFormula()
'shortcut: Ctrl+Shift+Z
    Dim rng As Range
    Dim objData As DataObject

    If TypeName(Selection) = "Range" Then
        Set rng = Selection(1)

        If rng.Formula Like "=*" Then

            Set objData = New DataObject
            objData.SetText rng.FormulaLocal
            objData.PutInClipboard

            MsgBox "The formula is copied to the clipboard", vbInformation
        End If
    End If

End Sub

Artik