Copy range to every nth cell

[Dokat]

Hi,

I have a spreadsheet where i would like to copy values between "G20:R20" and paste to every 14th cell in the same row (20). I am using below code. its running but doesn't change the values and paste the copy range. Can anyone help me with below code?

Sub CopyDateHeaders()
'Modified  7/3/2023  1:53:43 AM  EST
Application.ScreenUpdating = False

Dim cpy As range
Dim lastRow As Long

With Worksheets("Summary by 33")
    lastRow = 20
    Set cpy = .range("G20:R20")

    For colx = 20 To 188 Step 14
        .range(.Cells(20, colx), .Cells(lastRow, colx)).Value = cpy.Value
    Next
    
End With

Application.ScreenUpdating = True

End Sub

 

[Dokat]

G20: 5/31/2

Just so I understand your request correctly, what cells should G20 and H20 (I'll figure out the rest from there) be placed in?

G20: 5/31/2017
H20: 5/31/2018
I20:5/31/2019 and do on.


14 column after g20 which is T20
T20: 5/31/2017
U20: 5/31/2018
V20: 5/31/2019 and so on. Hope this clarifies. I also shared the sample file

 

[Dokat]

Just so I understand your request correctly, what cells should G20 and H20 (I'll figure out the rest from there) be placed in?

The issue i am coming across is it is just copy pasting the value in G20 to every 14th cell, not the full range from G20:R20.

 

[Rick Rothstein]

G20: 5/31/2

G20: 5/31/2017
H20: 5/31/2018
I20:5/31/2019 and do on.


14 column after g20 which is T20
T20: 5/31/2017
U20: 5/31/2018
V20: 5/31/2019 and so on. Hope this clarifies. I also shared the sample file

Oh, you want to copy the entire range, in tact, over 14 columns from where it is now (that is not what "copy range to every nth cell" meant to me). But that does not make sense... you already have data in cells T20:AE20. Or are you trying to overwrite that data?

 

[Dokat]

Oh, you want to copy the entire range, in tact, over 14 columns from where it is now (that is not what "copy range to every nth cell" meant to me). But that does not make sense... you already have data in cells T20:AE20. Or are you trying to overwrite that data?

Sorry for the confusion, yes i would like to overwrite that data, because the values in G20:R20 changes.

 

[Rick Rothstein]

Then you do not need a loop, just assign the one range to the other location with this single line of code..

Range("T20:AE20").Value = Range("G20:R20").Value

 

[Dokat]

Then you do not need a loop, just assign the one range to the other location with this single line of code..

Range("T20:AE20").Value = Range("G20:R20").Value

I will have to repeat this exercise all the way through column GE. I thought there might be easier way to do with loop rather than manually writing above code multiple times

 

[Rick Rothstein]

Ah, now I think I see what you were trying to do with your original code... you want to copy the values in G20:R20 over and over again. I am pretty sure this is the loop you will want to use (I took the 187 from your first post assuming it is correct)...

        For colx = 20 To 187 Step 14
            .Cells(20, colx).Resize(, 12).Value = .Range("G20:R20").Value
        Next

 

[Dokat]

Ah, now I think I see what you were trying to do with your original code... you want to copy the values in G20:R20 over and over again. I am pretty sure this is the loop you will want to use (I took the 187 from your first post assuming it is correct)...

        For colx = 20 To 187 Step 14
            .Cells(20, colx).Resize(, 12).Value = .Range("G20:R20").Value
        Next

Yes, thank you this worked perfectly i had to change 14 to 13 and that worked.