excelvbapaste

Paste Link with absolute references for Rows

I use the macro below to paste link a range, which is already in memory, with relative references for each copied cell.

I need column relative and row absolute, like hitting F2 to activate the cell then hitting F4 twice to make the row absolute.

Sub PasteLink()
    Selection.Activate
    ActiveSheet.Paste Link:=True
End Sub
Solution

  • Microsoft documentation:

    Application.ConvertFormula method (Excel)

    Sub PasteLink()

    Dim c As Range
    ActiveSheet.Paste Link:=True
    For Each c In Selection
        c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlAbsRowRelColumn)
    Next
    
End Sub