Copying Formulas

rzf sends a question to which I can’t find a suitable answer. Maybe you can help. The problem is to copy the formula from one cell to another, but not adjust the ranges relatively. The other constraints are that the cell must be copied normally (e.g. Cntl+C) and that absolute values can’t be required.

If A10 contains the formula =SUM(A1:A9), and the user copies A10, the macro must paste =SUM(A1:A9) in whichever cell the user chooses, say B10.

I think the meat of this problem is getting the “Range Object” from the clipboard. I doubt the clipboard contains a range object, but it certainly contains the address and the formula.

If you have an idea, post a comment. Thanks.

Posted in Uncategorized

25 thoughts on “Copying Formulas

  1. Are there any constraints ? ’cause we could use Application.OnKey to “take over” Ctrl+C, maybe store a public variable (ActiveCell), which can be used later when doing the paste ?

  2. I actually made an error in a work sheet because of this, but if you

    select the cell, F2 (or forumla bar), copy the text, the esc

    select ne cell
    then paste, or paste as unicode

    is this a solution? or do you want a macro?

  3. What about this?

    Worksheets(“Sheet1?).Range(“d1?).Formula = Worksheets(“Sheet1?).Range(“A11?).Formula


  4. Well, I don’t think that you would want to override the normal Ctrl+C and Ctrl+V commands in Excel, as that would be confusing.

    How about using the following two subroutines, and assigning the buttons Ctrl+Shift+C to launch ‘CopyFormulas’ and Ctrl+Shift+V to ‘PasteFormulas’?

    Of course, a nice addition would be to add some user prompts like “Those cells contain data! Are you sure you want to overwrite them?”

    Option Explicit

    Public sFormula(1 To 65536, 1 To 256) As String
    Public m As Long, n As Long

    Sub CopyFormulas()
    Dim j As Long, i As Long

    If TypeName(Selection) <> “Range” Then
      Exit Sub
    End If

    m = Selection.Rows.count
    n = Selection.Columns.count

    ‘Read the text in cells into an array
    For i = 1 To m
      For j = 1 To n
        If Selection.Cells(i, j).HasFormula Then _
        sFormula(i, j) = Selection.Cells(i, j).Formula
      Next j
    Next i

    End Sub

    Sub PasteFormulas()
    Dim j As Long, i As Long
    Dim rTarget As Range, rng As Range

    If TypeName(Selection) <> “Range” Then
      Exit Sub
    End If

    ‘Prompt the user for the output range to place the comments
    Set rTarget = Selection.Cells(1, 1)
    ‘Insert comments in the target range
    ‘Note that the shape of the output range will be identical to the input range
    For i = 1 To m
      For j = 1 To n
        Set rng = rTarget.Offset(i – 1, j – 1)
        If Not IsEmpty(sFormula(i, j)) Then
          rng.Formula = sFormula(i, j)
        End If
      Next j
    Next i
    End Sub

  5. When you copy data to the clipboard, it is stored in SYLK format. SYLK is a text-based representation of various aspects of the cell, including the formula, the number format, the cell formatting, borders, and the address of the current cell.

    Addresses are stored in R1C1 format, not A1 format.

    So if you copy a formula with a relative address to the clipboard, what gets stored is something like R[+3]C[-2], rows and columns relative to the current cell.

    When you paste into another cell, if the clipboard has SYLK-format data on it, Excel uses it. If there’s just text, Excel uses that.

    So, someone could write a program that monitors the clipboard for data in SYLK format, and when it sees a copy from Excel, converts the SYLK data with the R1C1 notation to text data in A1 notation. That way when the paste occurs, it puts the proper data into the cell.

    But I’m not sure if this would work; for Excel-to-Excel copies it may just skip the clipboard entirely.

  6. I will describe two non-macro methods that could be programmed. I’m sure that neither needs absolute references, but I’m not sure what you mean about CTRL-C, so I’ll just describe how you would do it manually.

    METHOD 1.
    (1) Copy A10 to anywhere, say D1. The results will be gibberish, but don’t worry.
    (2) Move A10 to the target cell, say B10.
    (3) Copy D1 back to A10. Clear D1.
    (4) Voila. B10 and A10 both have the same formula.

    METHOD 2.
    (1) Place an apostrophe at the start of the formula in A10. ‘=SUM(A1:A9)
    (2) Copy A10 to B10.
    (3) Delete the apostrophes from both A10 and B10.
    (4) Presto. B10 and A10 both have the same formula.

  7. Ross, I did used to use the windows clipboard to do this, but it did get to be a pain to make sure got it all when the formula in the cell wrapped; figured there had to be a better way.

    Matt, the only limitation I can see is that you have to know you’re going to do this paste before you do the copy, but I almost always do. I believe this’ll be a great fit in my macro arsenal. As far as prompting the user, most people won’t touch my Excel after they see my custom toolbar, so there’s no worries there.

    Jonathan, the approach sounds solid and has the plus of working as fast as a regular paste. However, if you look at my original code below, you can probably guess that’s way beyond my ability.

    This is the original sloppy code I’m going to replace. It works, but has two big limitations. First, it can only handle one formula at a time and, second, it’s easy to accidentally cause a circular reference. I may still salvage the approach of pasting the link and then tracing back to the orignal cell, but it’s clearly too much for this simple purpose.

    Sub FillExactFormulas()
    Dim Cell As Range
    Dim PasteArea As Range
    Dim OldState As Integer
    Dim OldFormula As String
    Dim LinkFormula As String
    Dim NewFormula As String
    Dim SelectedCell As Range

    Application.ScreenUpdating = False
    OldState = Application.Calculation
    On Error Resume Next

    Set PasteArea = Selection
    Set SelectedCell = ActiveCell

    OldFormula = ActiveCell.Formula ‘original formula in cell
    ActiveSheet.Paste Link:=True ‘overwrite formula
    Application.Calculation = xlCalculationManual ‘this turns cutcopymode off

    LinkFormula = ActiveCell.Formula ‘pasted formula is reference to cell with formula desired
    If LinkFormula = OldFormula Then ‘if this is true, that means no cell was copied in the first place, so exit
    Application.Calculation = OldState
    Exit Sub
    End If

    If Selection.Cells.Count > PasteArea.Cells.Count Then ‘if true, more than one cell was in the copy queue, so this resets the pastearea dimensions
    MsgBox “You can only use this function on one formula at a time. All cells have been filled with only the first formula in your selection.”, vbExclamation, “Too Many Formulas”
    Set PasteArea = Selection
    End If

    If Application.WorksheetFunction.Substitute(ActiveCell.Address, “$”, “”) = Mid(Application.WorksheetFunction.Substitute(ActiveCell.Formula, “$”, “”), 2, 10000) Then ‘if this is true, that means you tried to paste on top of the cell you were copying, so reset it to original formula instead of the pasted circular link to itself
    ActiveCell.Formula = OldFormula
    End If

    If Application.WorksheetFunction.Substitute(ActiveCell.Address, “$”, “”) = Mid(Application.WorksheetFunction.Substitute(LinkFormula, “$”, “”), 2, 10000) Then ‘if you pasted onto the cell you were copying, you don’t need to trace the formula back one step
    NewFormula = ActiveCell.Formula
    NewFormula = Range(ActiveCell.Formula).Formula
    End If

    PasteArea.Formula = “‘” & NewFormula

    For Each Cell In PasteArea
    Cell.Formula = Cell.Formula

    Application.Calculation = OldState
    End Sub

  8. If you are wanting to paste the formula *below* the existing one, you can use the shortcut key “Ctrl-‘” and avoid macros altogether.

    I was going to suggest a macro solution involving Range.PasteSpecial(xlPasteFormulas), but that method adjusts the formula during the paste.

  9. Press F2 to go into edit mode in the cell the formula is in. Select all the whole formula and press Ctrl & C (Copy). Go to the cell you want the formula to go in, press F2, delete anything already in the cell and press Ctrl & V (Paste).

  10. You could also do it this way, without any macro’s:

    1. Press F2 on A10
    2. Press Shift+Home to mark the content of the cell
    3. Press Ctrl+C to copy the content
    4. Press Esc or Tab
    5. Go to the new cell and press Ctrl+V to insert Your formula.

  11. Why doesn’t some one suggest the old F2 copy paste method!?

    what about this then, it’s got to be the easyiest way, or am i missing somthing????

    Decs section
    Global MyFormula As String

    the these to subs two which you assin as short cut

    Sub CopyMyFormula() ‘ to copy,
    MyFormula = ActiveCell.Formula
    End Sub

    Sub PasteMyFormula() ‘ to paste
    ActiveCell.Formula = MyFormula
    End Sub

    Now you can add a lot of stuff to this code to modify it how you like, but this does the job does it not?

    I guess if you whanted to have muilt options to paste you could (say load to a collection/array), but you would also need to call a user form to dispaly the options, a bit like the windows CB does?

    Any good?

  12. If you only want to copy the formula from the cell above, and do not want the cell references updated, try Ctrl+’

  13. This works… it prompts the user to select a destination cell… I borrowed the function from somewhere (probably one of your sites).

    Sub CopyMyFormula()
    Dim oRange As Excel.Range
    myformula = ActiveCell.Formula
    Set oRange = RangeUserSelect(“Please select the cell to Paste to “, , Range(“a1?))
    ActiveCell.Formula = myformula
    End Sub

    Function RangeUserSelect(sPrompt As String, Optional sTitle As String = “Select a Cell”, Optional oDefaultRange As Excel.Range) As Excel.Range
    On Error Resume Next
    ‘Display the Input Box
    If oDefaultRange Is Nothing Then
    If ActiveCell Is Nothing Then
    Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, , , , , , 8)
    Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, ActiveCell.Address, , , , , 8)
    End If
    Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, oDefaultRange.Address, , , , , 8)
    End If
    On Error GoTo 0
    End Function

  14. 1) In cell A11 hold down: Ctrl + ‘ (that Control and the apostrophe)
    2) Hit ENTER

    This will copy the formula from the cell above it.

    Drag cell A11 to the desired location

  15. I just open note pad.
    Then I copy and paste to notepad and then copy and paste back to excel. Ctrl+C and Cntrl+V both times.

  16. I just open note pad.
    Then I copy and paste to notepad and then copy and paste back to excel. Ctrl+C and Cntrl+V both times.

  17. I just open note pad.
    Then I copy and paste to notepad and then copy and paste back to excel. Ctrl+C and Cntrl+V both times.

  18. I just open note pad.
    Then I copy and paste to notepad and then copy and paste back to excel. Ctrl+C and Cntrl+V both times.

  19. I just open note pad.
    Then I copy and paste to notepad and then copy and paste back to excel. Ctrl+C and Cntrl+V both times.

  20. The way that I do this in practice is with the F2/Copy Formula/Paste Formula method that has been mentioned a bunch of times.

    Barring that, my next thought was that if we could use your Application class example to trap the Copy event and then modify what actually gets stored in to clipboard. However, I couldn’t find a Copy event anywhere.

  21. A way to avoid to deletion of references already established in the new cell (pasting causes “#REF!”) is a variation of what was posted by Robert Bursey (June 22, 2004)

    Example copy A1 to B2 keeping all formulas referring to both cells intact:

    (1) Copy A1 to a relatively clear position (where no cell reference is off the edge), say D10.
    (2) Move D10 the same relative position as the target cell is to the original (down 1, right 1) E11.
    (3) Copy E11 to B2.
    (4) Clear E11.

    This will also work for moving a range of cells too. A macro of this will need to check that the two temporary positions are empty.

  22. To copy/paste Formulas from a range of cells without changing the Absolute or Relative references:

    1. Select the range of cells containing the formulas and press Ctrl+H.

    2. In the Find what box, type the = sign.

    3. In the Replace with box, type the # sign (to change the formulas to text).

    4. Click Replace All, and then click Close.

    5. Copy and paste the cells to a new location.

    6. Repeat steps 1 through 3, reversing the # and = signs (to change the text to formulas).

    From Josel Robins:

  23. All you have to do is add $ before the number or letter that you wish to rmain the same.

    So, lets use the example in original question (shown below).

    If A10 contains the formula =SUM(A1:A9), and the user copies A10, the macro must paste =SUM(A1:A9) in whichever cell the user chooses, say B10.

    In order to do this the formula should read


    and when it is copied it would read the same.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.