Using VBA to take the data from a column and space it out into a row with 4 empty cells in between

JennaSmith495 

Here you go. Modify the constants at the beginning for your setup.

Sub CopyAndSpace()
    ' Change the following as needed
    Const SourceSheet = "Sheet1"
    Const FirstSourceRow = 1
    Const SourceCol = "C"
    Const TargetSheet = "Sheet2"
    Const TargetRow = 2
    Const Interval = 5
    ' Variables
    Dim WSource As Worksheet
    Dim WTarget As Worksheet
    Dim SourceRow As Long
    Dim LastSourceRow As Long
    Dim TargetCol As Long
    ' Code to copy cells
    Application.ScreenUpdating = False
    Set WSource = Worksheets(SourceSheet)
    Set WTarget = Worksheets(TargetSheet)
    LastSourceRow = WSource.Cells(WSource.Rows.Count, SourceCol).End(xlUp).Row
    For SourceRow = FirstSourceRow To LastSourceRow
        TargetCol = TargetCol + Interval
        WTarget.Cells(TargetRow, TargetCol).Value = WSource.Cells(SourceRow, SourceCol).Value
    Next SourceRow
    Application.ScreenUpdating = True
End Sub