Copying formulas between sheets and keeping original ref

[Walter]

Hi all

I have been trying to copy some formulas from a tab to another in the same
worksheet. what i want to do is to keep the original references of the
formulas. If i do a simple copy and paste, the resulting formulas will point
to different cells.

One way i devised to overcome this was to
1) cut and paste the formulas into the other tab
2) change all formulas into strings
3) copy strings back to the original place
4) back from strings to formulas

but this has the negative effect that all other cells linking to the ones i
cut, will be re-referenced to the new ones, that is something i dont want.

I hope this is not too confusing, but is there any way to copy formulas and
keep where they originally pointed to?
many thanks for your help!!

 

[Stefi]

Try to use absolute references!

Regards,
Stefi

„Walter†ezt írta:

 

[Pete_UK]

Give an example of the formulae which are causing problems.

Pete

 

[Walter]

I cant do that.

Try to use absolute references!

Regards,
Stefi

„Walter†ezt írta:

 

[Stefi]

Then try a solution of this kind:
Sub test()
Cells(ActiveCell.Row, ActiveCell.Column + 1).Formula = _
"=" & Mid(ActiveCell.Formula, 2)
End Sub

This sub copies the formula in the active cell into the cell same row, next
column, without changing the original references. Adjust it to your needs!

Regards,
Stefi


„Walter†ezt írta:

 

[Walter]

Hi Pete.
Formulas are different, they might be easy like =A2 or more complicated like
=vlookup or offset or some calculations, with both absolute or relative
references.

i guess the point i want to make here is simply that i would like to copy
the formula into another sheet keeping the same reference (formula in
sheet1!A1 points to D5? i want to copy it into Sheet3!X3 so that it still
points to Sheet1!D5 - note that the name of the tab was not included in the
original formula).

And i need to copy it, not modify the existing ones! The reason being that
it is a cash flow model and i need to select an area with more than 1000
cells in it


Hope this clarifies a bit more my need .. thanks!

 

[Walter]

thanks very much Stefi, that might be a solution
how can i adjust the code to add to each reference in the formulas the name
of the sheet? (problem is that some formulas are like =A5 some other point
to =sheet2!a5 so i dont want to add it two times..)

thanks again

 

[Stefi]

If the original formula contains sheet name then the result cell shall also
contain the same sheet name.

Stefi

„Walter†ezt írta:

 

[Gord Dibben]

Select the source cells

Edit>Replace

What: =

With: ^^^

Replace all

Copy to destination sheet. Reverse the process on both sheets.


Gord Dibben MS Excel MVP