Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA Macro shortcut to "paste special formula"

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Macro shortcut to "paste special formula"

    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 3 1 1
    2 3 1 1
    2 3 1 1
    2 3 1 1
    2 3 1 1
    2 3 1 1
    2 3 1 1
    2 3 1 1
    16 24 8 1
    1
    1
    1
    8
    ^^ Code breaks above

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA Macro shortcut to "paste special formula"

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro shortcut to "paste special formula"

    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

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,600
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: VBA Macro shortcut to "paste special formula"

    Wouldn't this do what you want?

    Code:
    destinationRange.FormulaR1C1= sourceRange.FormulaR1C1
    Last edited by mikerickson; Jul 15th, 2019 at 03:38 PM.

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro shortcut to "paste special formula"

    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

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA Macro shortcut to "paste special formula"

    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.


    Quote Originally Posted by aditya8090 View Post
    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro shortcut to "paste special formula"

    Quote Originally Posted by My Aswer Is This View Post
    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.

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,600
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: VBA Macro shortcut to "paste special formula"

    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.

  9. #9
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro shortcut to "paste special formula"

    Quote Originally Posted by mikerickson View Post
    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.

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,600
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: VBA Macro shortcut to "paste special formula"

    Something like


    Code:
    With Selection
        .Offset(0,1).FormualR1C1 = .FormulaR1C1
    End With

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •