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" wrote:

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:

I cant do that.

"Stefi" wrote:

Try to use absolute references!

Regards,
Stefi

€žWalter€� ezt Ã*rta:

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!!