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.

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 ?

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?

What about this?

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

hun?

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

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.

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.

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

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.

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

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.

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?

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

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

That’s my blody code with bits added on!

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

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.

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.

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.

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

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

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.

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

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.