Hi Henk

Try This - it asks you for the range you want to copy and the start cell of
where you want to copy the formulae to.

Sub CopyFormulae()
Dim myCopyRng As String, myDestRng As String
Dim cell As Range
myCopyRng = InputBox("Enter the range that you want to copy." & _
"e.g. A1:A20", "Copy Range")
If myCopyRng = "" Then Exit Sub
myDestRng = InputBox("Enter the start cell where you want to paste to." & _
"e.g. B1", "Paste Range")
If myDestRng = "" Then Exit Sub
x = 0
For Each cell In Range(myCopyRng)
Range(myDestRng).Offset(x, 0).Formula = cell.Formula
x = x + 1
Next
End Sub

"Henk" wrote:

Trevor,

I am afraid that is not the solution. What I need is two ranges with axactly
the same formula's. So copy for instance formula =(a1*(a2+b7)) from cell B5to
cell AX2256 should result in the fomula =(a1*(a2+b7)) in cell AX2256. So,
the same result as if you move the cell, but leving the original in place
(cell B5).

For your information : I often have to copy very complicated formula's like
this and then change them slightly afterwards. For instance, changing the
name of a named range only in formula's like

=VLOOKUP(A63,SearchDatabaseXXX,6,False),
=VLOOKUP(B63,SearchDatabaseXXX,6,False),
=VLOOKUP(C63,SearchDatabaseXXX,6,False) etecetera

change to

=VLOOKUP(A63,SearchDatabaseYYY,6,False),
=VLOOKUP(B63,SearchDatabaseYYY,6,False),
=VLOOKUP(C63,SearchDatabaseYYY,6,False)

Regards,

Henk

"Trevor Williams" wrote:

Hi Henk

If I've understood your question correctly, you want 2 identical ranges...
Can you not just set the second range equal the first?

e.g. it data is in A1, the formula in B1 would be "=A1" (without quotes)

Trevor

"Henk" wrote:

I am looking for a way to to select a range of cells with formula's and then
to MOPY it to another place. Result : two ranges with exaclty the same
formula's whether the cell adresses used in the formula's are relative or
absolute. In fact a bit of copy and a bit of move.

Regards,

Henk