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

   hun?

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
   PasteArea.Cells(1).Select

   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
   PasteArea.Select
   SelectedCell.Activate
   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
   Else
   NewFormula = Range(ActiveCell.Formula).Formula
   End If

   PasteArea.Formula = “‘” & NewFormula

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

   PasteArea.Select
   SelectedCell.Activate
   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?))
    oRange.Select
    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)
    Else
    Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, ActiveCell.Address, , , , , 8)
    End If
    Else
    Set RangeUserSelect = Application.InputBox(sPrompt, sTitle, oDefaultRange.Address, , , , , 8)
    End If
    On Error GoTo 0
    End Function

14. That’s my blody code with bits added on!

15. 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

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. 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.

21. 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.

22. Andy: No copy event that I know of.

23. 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.

24. 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:
    http://www.exceltip.com/st/Copying_Formulas_from_a_Range_of_Cells_Without_Changing_the_Absolute_or_Relative_References/694.html

25. 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

    =SUM($A$1:$A$9)

    and when it is copied it would read the same.

---

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