View Full Version : Solved: Copying Formulas

04-09-2005, 05:59 AM
I know that there has to be a way, but I don't know how, I would like to copy multiple formulas and insert them in a different place on the worksheet or on another sheet. when I copy the cells and paste special the formulas it paste the formulas but changes the value of the formula.

04-09-2005, 08:08 AM
Hi Erays

Sounds like you may need to make your formula references absolute. Say you have this fomula in cell C1


Click on A1 in the formula bar - now press F4. The "A" will now be surrounded with dollar signs and should look like this


You've now made the formula reference to cell A1 absolute. When you copy and paste it will remain as A1. Do the same for B1.

If you just wanted the column reference to remain absolute then it would be


and for rows it would be


Does this help?


04-09-2005, 08:19 AM
If the formulas are already on the page will I have to change each one and then copy them

04-09-2005, 09:20 AM

I thought this code might work

Sub ChangeCellRefs()
Dim myRng As Range
Dim c As Range

Set myRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

For Each c In myRng
Application.ConvertFormula Formula:=c.Formula, fromReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute
End Sub

but it's not - and I can't figure out why :dunno .

See if it works for you - I'll keep trying.


04-09-2005, 01:57 PM
I'm not over-familiar with this but it appears to work this way ...

c.Formula = Application.ConvertFormula(Formula:=c.Formula, fromReferenceStyle:=xlA1, toReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

04-10-2005, 08:43 AM
Nice one Tony - I was not really familiar with that myself so it was a bit of a learning curve!