Cell references absolute or relative

[Robbie_Goodwin]

For single Cells, Excel offers two relevant methods of Copying to other locations.

The default Copy and Paste commands give the target cell the same formula, with Cell references adjusted. This is not a pure Copy.

The other choice is to Copy not the entire Cell but only the formula it contains, in which case Paste will give the target Cell unadjusted references. This is a pure Copy.

(Quite separately, how should we distinguish between an entire Cell and the formula it contains?)

My interest is in Ranges, not individual Cells.

My experience, since about Excel 4 in about 1990, is that with a Range, the only choice is to use the default Copy and Paste commands, giving the same formula, with Cell references adjusted, which means two or more Cells must be dealt with manually and individually.

I seek a means of Copying “only the formulae” for a Range, to give the target cell the same formula, with Cell references unadjusted.
Around the time of Excel 4, non-MS spreadsheet rival SuperCalc found this “problem” trivial, which suggests MS ought to, also. Two generations ago SuperCalc didn’t care whether the source was a single Cell or a Range… it always offered the choice of Copying or Replicating, one giving pure and the other relative offset Cell references. (Quite separately, SuperCalc shot itself in the foot with v4 and went on to chop off its own shins, knees, thighs, pelvis and torso with v5. That’s wholly beside the point.)

(https://techcommunity.microsoft.com/t5/excel/copy-and-paste-a-range-of-cells-and-keep-formula-eferen...) is the closest useful reference I could find here, where Sergei Baklan suggested a workaround to a similar problem:

- select entire Range

- Ctrl+H and replace = on #

- Copy/Paste the range on new location

- select Range, Ctrl+H and replace # on = (for both Ranges)

Am I missing something, or is  Sergei’s suggestion really the least clunky solution MS can offer?

[#2]

@Robbie_Goodwin 

If you can't make the cell references absolute ($A$2 instead if A2), Sergei's tip is the best you can do without VBA.

You might use the following macro. If you store it in a module in your personal macro workbook Personal.xlsb, it will be available in all workbooks, and you can assign it to (for example) a Quick Access Toolbar button.

Sub CopyFormulas()
    Dim rngSource As Range
    Dim rngTarget As Range
    On Error Resume Next
    Set rngSource = Application.InputBox(Prompt:="Select the source range", _
        Default:=Selection.Address, Type:=8)
    If rngSource Is Nothing Then
        Beep
        Exit Sub
    End If
    Set rngTarget = Application.InputBox(Prompt:="Select the target range", _
        Type:=8)
    If rngTarget Is Nothing Then
        Beep
        Exit Sub
    End If
    rngTarget.Formula = rngSource.Formula
End Sub

[#3]

@Robbie_Goodwin 

My solution would be to lease Microsoft 365 and consign the concept of relative referencing to the trash can of history :)

Neither the processing of arrays nor lists in Tables in 365 requires the concept of relative referencing, provided you apply a Name to the Range.  On the occasions when terms within the formula do need to be evaluated individually the '@' operator is usually sufficient.