Unfortunately I couldn’t find the answer to the below in the other questions – my problem is related to copying and pasting a formula that would use relative cell references instead of dynamic ones.

The general problem with the workbook I’m working on is the fact that it contains of a couple of different sheets with a potential dynamic range change. To give a better outline:

* The column the formula has to be in, is based on an offset cell – I cannot give it a static value – and starts in row 2;
* The formula itself is as follows: `=CONCATENATE(LEFT(AA2,13), “:”, RIGHT(AA2,5)` (and the values to be concatenated will always appear in the AA column)

The macro is supposed to insert the formula in the offset cell, copy it and paste it in the entire column, so the cell in row 3 refers to cell AA3 and so on:

```
Sub Copy1()

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(, 1).Select

Dim rng as Range
Set rng = ActiveCell
rng.Select

rng.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(R2C27,13), "":"", RIGHT(R2C27,5))"
rng.Offset(1, 0).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PasteSpecial
Selection.EntireColumn.Select
Application.CutCopyMode = False

End Sub

```

The problem is – upon trying to insert exact name of the cell, the macro populates it with the following: 

```
=CONCATENATE(LEFT(‘AA2’,13), "":"", RIGHT(‘AA2’,5))

```

Because of the quotation marks, the formula doesn’t work.

Using the reference `R2C27` results with absolute values being copied and thus every single cell in the column refers to cell `AA2`.

Is there any possibility make it create, copy and paste relative reference instead of absolute?