vba paste exact formula from clipboard

yoninov

New Member
Joined
Mar 27, 2019
Messages
7
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.

VBA Code:
 Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

strPaste = DataObj.GetText(1)

Thank you so much for any guidance!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
@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.

VBA Code:
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.
 
Upvote 0
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)
 
Upvote 0
It appears to work perfectly well for me.
Copied B7:C10 with '=' in the formulas to C13:D16, see below.

Book1
BCD
712True
83False
955False
104False
11
12
1312True
143False
1555False
164False
Sheet3
Cell Formulas
RangeFormula
C7:C10C7=B9=55
D13:D16D13=B9=55


Is all your code in a Module?
 
Upvote 0
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
1580859333599.png
 
Upvote 0
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.
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top