move cell values in excel through vba

Question

I am working on an excel sheet and need to move the same range over and over again to the column "P" + 2

So the next range would be "C15:G15" to "P14". I'm looking for a slimmer solution than to repeat this code and change the ranges for hundreds of times..

ActiveWindow.SmallScroll Down:=-3
Range("C13:G13").Copy
Application.CutCopyMode = False
Selection.Copy
Range("P12").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
    , SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Answer 1

This quick snippet should walk down every second row in column C starting at row 13 till the last populated cell in column C.

Sub move_CG_to_PT()
    Dim rw As Long

    With Worksheets("Sheet4")  '<~~set this worksheet reference properly!
        For rw = 13 To .Cells(.Rows.Count, "C").End(xlUp).Row Step 2
            .Cells(rw - 1, "P").Resize(1, 5) = _
              .Cells(rw, "C").Resize(1, 5).Value
        Next rw
    End With
End Sub

This only transfers the values. If the formatting and/or theme is critical then those could be adjusted for with the following.

Sub move_CG_to_PT_w_Formatting()
    Dim rw As Long

    With Worksheets("Sheet4")  '<~~set this worksheet reference properly!
        For rw = 13 To .Cells(.Rows.Count, "C").End(xlUp).Row Step 2
            .Cells(rw, "C").Resize(1, 5).Copy _
                Destination:=.Cells(rw - 1, "P")
        Next rw
    End With
End Sub