View Full Version : Solved: Copying Formulas

Erays

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.

Glaswegian

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

=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

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

Glaswegian

04-09-2005, 09:20 AM

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

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

Regards

TonyJollans

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)

Glaswegian

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!

Regards

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.