VBA Macro shortcut to "paste special formula"

aditya8090

New Member
Joined
Jul 15, 2019
Messages
6
Hi all! I have been trying to write VBA macro to simulate the keystroke (Ctrl + Alt + v + f) to copy and paste formula from an excel cell. I'm a beginner, and so far I've been able to develop the functionality of copying the formula and pasting it relationally. However, the code breaks if it is pasted to a column having more or less elements than the column it was copied for. I've attached a graphical representation for the problem too. Please help me fix this. Thanks, in advance.

Code:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Offset(0, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Graphical representation (the formula is just summing up the elements - 16 is the sum of 2 eight times):
A B C D
2
311
2311
2311
2311
2311
2311
2311
2311
16
2481
1
1
1
8
^^ Code breaks above

<colgroup><col width="64" style="width: 48pt;" span="7"> <tbody> </tbody>
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
My question would be:
What Range do you want to sum and where do you want the results entered.

If your going to use Vba why copy the formula?

Vba can just put the results of the formula in a Range

Like this script sums up the Range("A1:A10")
And puts results in Range("B1")
Code:
Sub My_Formula()
'Modified 7/15/2019 3:19:44 PM  EDT
Dim ans As Long
ans = Application.WorksheetFunction.Sum(Range("A1:A10"))
Range("B1").Value = ans
End Sub
 
Upvote 0
Thanks for replying! I am working on a worksheet with a lot of for formulas and It would definitely be easier for me to just copy-paste the formula from the row beside instead of creating custom macros for the different formulas
 
Upvote 0
Hey! Thanks for replying. Can you assist with me how I'm supposed to incorporate it into the code. As of now, this is what I'm doing, and this doesn't seem to work. (I apologize if this is a stupid question, I've just started picking up macros)

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Offset(0, 0).destinationRange.FormulaR1C1 = SourceRange.FormulaR1C1.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
 
Upvote 0
So you have code that does not work and you want us to incorporate our code into your code?
And you have not shown us your formula. That would be hard for me to do.


Hey! Thanks for replying. Can you assist with me how I'm supposed to incorporate it into the code. As of now, this is what I'm doing, and this doesn't seem to work. (I apologize if this is a stupid question, I've just started picking up macros)

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Offset(0, 0).destinationRange.FormulaR1C1 = SourceRange.FormulaR1C1.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
 
Upvote 0
So you have code that does not work and you want us to incorporate our code into your code?
And you have not shown us your formula. That would be hard for me to do.

Thank for replying! My aim is to copy-paste any arbitrary formula through a simplified key binding, let us call it ctrl + c and ctrl + q. At present, if we want to copy-paste a special formula, we do ctrl + c and then ctrl + alt + v + f. The additional keys is what I want to remove. Talking about the formula, as I mentioned before, it can be any arbitrary formula. The macro doesn't need to worry about that. But for demonstrative purposes we can do sum right now. The main purpose of the macro is to simply copy-paste the special formula, the formula here could be anything (ranging from as simple as a+b to some hardcore finance formula). The macro shouldn't be affected by the kid of formula used. My code, as of now, breaks when I copy-paste a formula for a list having more or less items than the one copied from. For eg, if we have a formula calculating maybe the sum of a list of 10 numbers, if I copy that to a list of 11 numbers or 9 numbers, the macro breaks. Again, the formula used here as an example (of sum) isn't important, the code breaking is.
 
Upvote 0
The difficulty of your scheme is "where"

Currently,
Select source Range
Ctl+C
Select destination Range
Ctl+q

how do you want the user to designate the source and destination ranges.
 
Upvote 0
The difficulty of your scheme is "where"

Currently,
Select source Range
Ctl+C
Select destination Range
Ctl+q

how do you want the user to designate the source and destination ranges.


Let us focus more on the pasting part for now. We can do Ctrl + c like we usually do. For clarification, if there is a formula in, say, A10 which the user wishes to copy to B10, he simply goes to A10, enters ctrl + c, goes to B10, enters ctrl + q, and the formula has been pasted relationally.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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