Copying Formulas

[Erays]

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.

[Glaswegian]

Hi Erays

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

=SUM(A1:B1)

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

=SUM($A$1:B1)

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

=SUM($A1:B1)

and for rows it would be

=SUM(A$1:B1).

Does this help?

Regards

[Erays]

If the formulas are already on the page will I have to change each one and then copy them

[Glaswegian]

Hi

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
Next
End Sub

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

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

Regards

[TonyJollans]

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)

[Glaswegian]

Nice one Tony - I was not really familiar with that myself so it was a bit of a learning curve!

Regards