Hi,
Please give support on this. Code today in end, but it doesn't cover all
reference possibilities. The reason for all this is that I need to have
formulas pointing to the exact same cells in another sheet.

These following examples should be covered, pointing to the same cell with a
modified formula if needed. Without my code today, just moving (copy/paste)
the formula will casue:

=B3 ' If moving to sheet3 will point to wrong sheet,
not the cell in sheet1 but the new sheet3
=$A$4 ' If moving to sheet3 will point to wrong sheet, not
the cell in sheet1 but the new sheet3
=12*B3 ' If moving to sheet3 will point to wrong sheet, not
the cell in sheet1 but the new sheet3
= B3*A4 ' If moving to sheet3 will point to wrong sheet, not
the cell in sheet1 but the new sheet3
='Sheet1'!B8 'OK
='Sheet2'!B15 'OK
='Sheet2'!$A$10 'OK

The first 4 ex should on Sheet3 instead become
='Sheet1'!$B$3
='Sheet1'!$A$4
=12*'Sheet1'$B$3
= 'Sheet1'$B$3*'Sheet1'$A$4

Now I have code that ensure absolute references (se below). The last three
will be ok, but the two first will point to the cells in Sheet3.

='Sheet1'!$B$3 'OK
='Sheet1'!$A$4 'OK
='Sheet1'12'*B$3 'Make ERROR
= 'Sheet1'$B$3*$A$4 'Point to WRONG CELL/SHEET

Here is my code today. It work sometimes but only if the sheet reference
should be first and it only is one.

Sub AbsoluteReferenceSameSheet()
On Error GoTo ErrorHandler
Dim rng As Range

'-------- Make absloute references !
For Each rng In Range("A1:F10").SpecialCells(xlCellTypeFormulas)
With rng
If .HasArray Then
.FormulaArray =
Application.ConvertFormula(.FormulaArray, xlA1, xlA1, xlAbsolute)
Else
.Formula = Application.ConvertFormula(.Formula, xlA1,
xlA1, xlAbsolute)
End If
End With

'-------- Check if there already is a sheet reference !
Dim adr As String 'adr = the address of the cell
Dim frm As String 'frm = formula pointing to another cell
adr = rng.Address
If InStr(1, rng.Cells.Formula, "!") = 0 Then

'-------- If there isn't a sheet reference it is a sheet1 cell reference !
Put a 'Sheet1'! string in front
frm = "=" & Sheet1.Name & "!" & Mid(rng.Cells.Formula, 2)
Debug.Print " Cell: " & adr
Debug.Print " Formula: " & frm
Let rng.Cells.Formula = frm
End If
Next rng

' -------Paste the cells to Sheet3!
Sheet1.Range("A1:F10").SpecialCells(xlCellTypeVisible).Cells.Copy
Sheet3.Range("A1:F10").PasteSpecial (xlPasteAll)

Exit Sub
ErrorHandler:
Debug.Print "E R R O R AbsoluteReferenceSameSheet"
On Error GoTo 0
End Sub


/Kind regards