vba paste exact formula from clipboard

[yoninov]

What I would like to do is copy (by ctrl C) a cell or range of cells so that it goes into the clipboard. Then when I run the macro (I will create a shortcut) it will paste it to the selected cell (or if a range will paste down / across / range - the same way regular ctrl C / ctrl V works).

However, the paste would be the EXACT formula in the cell I copied. Without changing the references. Kinda like cut and paste without losing the original formula.

For the sake of being overly clear: I have this formula in cell A4 "=A1+A2". I want to be able to press ctrl C when I selected A4 (not in edit mode). Then select B4 and run my macro and it will put "=A1+A2" in B4. But also be able to use the macro with a range.

I tried this, but it only gives me the value.

 Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

strPaste = DataObj.GetText(1)

Thank you so much for any guidance!

 

[Snakehips]

@yoninov
I've been having a little play with this. Because of the way Excel uses the clipboard and does not retain data etc I don't think you can use normal Copy & Paste.
Maybe if you are happy to assign your own keyboard shortcuts you can utilise the following?
Copy to a code module.

Public MyArry() As Variant


Sub CopyAsIs()
Application.ScreenUpdating = False

    Selection.Replace What:="=", Replacement:="xyz999", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
MyArry = Selection

    Selection.Replace What:="xyz999", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Application.ScreenUpdating = True
    
End Sub


Sub PasteAsIs()
Dim Rws, Cls As Long
Application.ScreenUpdating = False
Rws = UBound(MyArry, 1)
Cls = UBound(MyArry, 2)

Selection.Cells(1, 1).Resize(Rws, Cls).Select
Selection = MyArry
   Selection.Replace What:="xyz999", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Application.ScreenUpdating = True
End Sub

Hope that helps.

 

[yoninov]

Thanks @Snakehips I see what your doing here, (quite cleaver I might add). Might be ok for now, but there has got to be a better way, right? Also, it would not work if you have "=" within the formula (like some sort of IF( statement.

Either way, I can't get this to work as is. I get a Run-time error '13': Type mismatch. Its happening on the MyArry = Selection line.

(I did put in the line "Public MyArry() As Variant" at the top)

 

[Snakehips]

It appears to work perfectly well for me.
Copied B7:C10 with '=' in the formulas to C13:D16, see below.

Book1
	B	C	D
7	12	True
8	3	False
9	55	False
10	4	False
11
12
13		12	True
14		3	False
15		55	False
16		4	False
Sheet3

Cell Formulas
Range	Formula
C7:C10	C7	=B9=55
D13:D16	D13	=B9=55

Is all your code in a Module?

 

[yoninov]

yes all the code in a module. Thinking it through, it should work when you have multiple "=" it would just replace each instance with "xyz999". Not sure why I keep getting the run-time error. Here is a screen shot

 

[Snakehips]

I have to say that I'm a bit puzzled. Mine works ok for a mix of formulas, constants, text, blanks etc.
Destination range adjusts to correct size regardless of how many cells are selected for the 'pasting'.
You can copy and paste between different sheets.
It doesn't re-create merged cells on paste but copying a mix of merged and single cells is not causing issue.

I don't see why it should make any difference but maybe substitute MyArry = Selection
with MyArry = Selection.Value
or even
Set MyRange = Range(Selection.Address)
MyArry = MyRange.Value
and see if that still errors.

 

[Akuini]

How about using an Inputbox?
Select the range to copy, run the code, an Inputbox will show up, select a cell to paste, click OK.

Sub CopyFormula()
'https://www.mrexcel.com/board/threads/vba-paste-exact-formula-from-clipboard.1122954/
Dim c As Range, va
    
    On Error GoTo SKIP:
    va = Application.Selection.Formula
    Set c = Application.InputBox("Select a cell to paste:", Title, Type:=8)

    If Selection.Cells.Count = 1 Then
        c = va
    Else
        c.Resize(UBound(va, 1), UBound(va, 2)) = va
    End If
    Application.CutCopyMode = False
    Application.Goto c.Cells(1, 1)

Exit Sub

SKIP:
    If err.Number <> 424 Then
        MsgBox "Error found: " & err.Description
    End If
    
    Application.CutCopyMode = False
    
End Sub

 

[Snakehips]

@Akuini
Very good!