VBA excel copy above cells when intersect

Question

I need to make Excel copy specific Range to the row, which is currently selected.

For example I have data like this:

And when I select A3, then I need to make Excel copy automatically Range C2:F2 to C3:F3, like this

And so on... If I select A4, I need to copy C3:F3 to C4:F4...

How is this possible please?

Answer 1

This sounds more like autofill to me. You should probable fill down from C2:F2.

Range.AutoFill

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Target.Row = 1 And Not Intersect(Target, Range("A2", Columns("A"))) Is Nothing Then
        With Target.Offset(-1).EntireRow.Range("C1:F1")
            .AutoFill Destination:=.Resize(2), Type:=xlFillDefault
        End With
    End If
End Sub

Range.Copy

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Target.Row = 1 And Not Intersect(Target, Range("A2", Columns("A"))) Is Nothing Then
        With Target.EntireRow.Range("C1:F1")
            .Offset(-1).Copy Destination:=.Cells
        End With
    End If
End Sub