VBA Concatenate Issue

[Roballistic]

I'm not understanding why the code below will generate a #REF! error on the last cell identified to be concatenated (Row 3: Minnie Mouse), yet when I go into where the formula is copied, and I physically replace it with the cell I reference, it gets me exactly what I want (Row 2: Mickey Mouse).

Sheets("Temp2").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Index_LN_FN_SSN"
Range("A2").Select

'=CONCAT(B2,R2:S2,O2)
ActiveCell.FormulaR1C1 = "=CONCAT(RC[1],RC[17],RC[18],RC[14])"
Range("A2").Select
Selection.Copy
Range("A2:A501").Select
ActiveSheet.Paste

 

[BigBeachBananas]

Try Filldown...

UNTESTED CODE- Try this on a COPY of your workbook.

Sub ConcatenateColumnsOptimized()
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Temp2")
    ws.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ws.Range("A1").Value = "Index_LN_FN_SSN"
    ws.Range("A2").FormulaR1C1 = "=CONCAT(RC[1],RC[17],RC[18],RC[14])"
    
    ' Fill down the formula from cell A2 to cell A501
    ws.Range("A2:A501").FillDown
End Sub

 

[Roballistic]

Try Filldown...

UNTESTED CODE- Try this on a COPY of your workbook.

Sub ConcatenateColumnsOptimized()
    Dim ws As Worksheet
   
    Set ws = ThisWorkbook.Sheets("Temp2")
    ws.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ws.Range("A1").Value = "Index_LN_FN_SSN"
    ws.Range("A2").FormulaR1C1 = "=CONCAT(RC[1],RC[17],RC[18],RC[14])"
   
    ' Fill down the formula from cell A2 to cell A501
    ws.Range("A2:A501").FillDown
End Sub

You can consider this now tested code as it worked as you coded above! Thanks for your help!