[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] phoo1()
[COLOR=blue]Dim[/COLOR] rngCopy [COLOR=blue]As[/COLOR] Range, rngDestination [COLOR=blue]As[/COLOR] Range
[COLOR=blue]Dim[/COLOR] arrFormulas()
[COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] Catch
[COLOR=blue]Set[/COLOR] rngCopy = Application.InputBox( _
prompt:="Copy range:", _
Title:="Copy", _
[COLOR=blue]Type[/COLOR]:=8)
[COLOR=blue]Set[/COLOR] rngDestination = Application.InputBox( _
prompt:="Paste cell:", _
Title:="Paste", _
[COLOR=blue]Type[/COLOR]:=8)
[COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] 0
arrFormulas = rngCopy.Formula
rngDestination.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count) = arrFormulas
Catch:
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
rngCopy.Copy
rngDestination.PasteSpecial xlFormats
ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, xlAbsolute, ActiveCell)
It's been a while . You're welcome! But now I just can't help posting a better answer.
There is a VBA method called ConvertFormula that can be used to toggle A1/R1C1 notation. We can exploit this method to convert ranges to absolute, e.g:
Code:ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, xlAbsolute, ActiveCell)
So using this method one could loop a range of cells and rewrite the formula in each cell, converting references to absolute in the process.