Copy range and paste to every nth column

[rjh3m8]

Hi, I am trying to copy a range of data and paste it to every 4th column. For instance, Row 1 has 25 columns of datapoints, I want each datapoint to be 4 columns apart. Then again for Row 2 data... Row 3 data... on and so forth. I have the following code I edited from somewhere else, but it is pasting only one row of data to one column, with each data point spaced 4 rows apart. Can anyone help me see where it's gone wrong?

Sub Copy_Paste_nth()
Dim i As Long, r As Long
Dim Rng As Range

With Sheets("Sheet1")
Set Rng = .Range("A1:Y1", .Range("A" & Rows.Count).End(xlUp))
End With
For i = 2 To Rng.Count * 4 Step 4
r = r + 1
Sheets("Sheet2").Range("C" & i).Value = Rng(r).Value
Next i
End Sub

 

[Fluff]

Ok, how about

Sub rjh3m()
   Dim i As Long, UsdRws As Long
   
   With Sheets("Sheet1")
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      For i = 1 To Range("DY2:EW2").Columns.Count
         Sheets("Sheet2").Cells(1, i * 4 - 3).Resize(UsdRws).Value = .Cells(1, i + 128).Resize(UsdRws).Value
      Next i
   End With
End Sub

 

[rjh3m8]

Ok, how about

Sub rjh3m()
   Dim i As Long, UsdRws As Long
  
   With Sheets("Sheet1")
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      For i = 1 To Range("DY2:EW2").Columns.Count
         Sheets("Sheet2").Cells(1, i * 4 - 3).Resize(UsdRws).Value = .Cells(1, i + 128).Resize(UsdRws).Value
      Next i
   End With
End Sub

YES! Thank you so very much.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[rjh3m8]

Glad to help & thanks for the feedback.

Sure thing.

One thing I'm noticing now, it DOES work for the intended range listed. When I change the range that I'm copying FROM though, it doesn't change... so maybe I'm changing the copy-from range incorrectly? I am assuming it is in the line "For i = 1 To Range("DY2:EW2").Columns.Count" and that I would change that range--is that correct? So far, it has not worked this way but I am new to VBA so I may be reading it wrong

 

[Fluff]

You would also need t change this part

.Cells(1, i + 128)