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

 

[Rick Rothstein]

Untested, but I am pretty sure this modified version of your macro should work...

Sub CopyDateHeaders()
    'Modified  7/3/2023  1:53:43 AM  EST
    Application.ScreenUpdating = False
   
    Dim cpy As Range
    Dim lastRow As Long, N As Long
   
    With Worksheets("Summary by 33")
        lastRow = 20
        Set cpy = .Range("G20:R20")
   
        For colx = 20 To 188 Step 14
            .Cells(20, colx).Value = cpy.Offset(0, N).Value
            N = N + 1
        Next
       
    End With
   
    Application.ScreenUpdating = True

End Sub

NOTE: I left it in just in case you had plans for using it later on, but as written, your code does not need/use the lastRow variable.

 

[Dokat]

Untested, but I am pretty sure this modified version of your macro should work...

Sub CopyDateHeaders()
    'Modified  7/3/2023  1:53:43 AM  EST
    Application.ScreenUpdating = False
 
    Dim cpy As Range
    Dim lastRow As Long, N As Long
 
    With Worksheets("Summary by 33")
        lastRow = 20
        Set cpy = .Range("G20:R20")
 
        For colx = 20 To 188 Step 14
            .Cells(20, colx).Value = cpy.Offset(0, N).Value
            N = N + 1
        Next
     
    End With
 
    Application.ScreenUpdating = True

End Sub

NOTE: I left it in just in case you had plans for using it later on, but as written, your code does not need/use the lastRow variaThanks for your response. Whe

Untested, but I am pretty sure this modified version of your macro should work...

Sub CopyDateHeaders()
    'Modified  7/3/2023  1:53:43 AM  EST
    Application.ScreenUpdating = False
  
    Dim cpy As Range
    Dim lastRow As Long, N As Long
  
    With Worksheets("Summary by 33")
        lastRow = 20
        Set cpy = .Range("G20:R20")
  
        For colx = 20 To 188 Step 14
            .Cells(20, colx).Value = cpy.Offset(0, N).Value
            N = N + 1
        Next
      
    End With
  
    Application.ScreenUpdating = True

End Sub

NOTE: I left it in just in case you had plans for using it later on, but as written, your code does not need/use the lastRow variable.

Thank you for your quick response. However when i ran the code it didnt copy paste the range (G20:R20) and didnt give any error message. I removed lastrow variable.

 

[Rick Rothstein]

Thank you for your quick response. However when i ran the code it didnt copy paste the range (G20:R20) and didnt give any error message. I removed lastrow variable.

What did it do?

 

[Dokat]

What did it do?

It only copy paste the value in R20 to T20. it didnt copy values s20:t20 and paste

 

[Rick Rothstein]

Sorry, my fault. Try changing this line of code...

.Cells(20, colx).Value = cpy.Offset(0, N).Value

to this...

.Cells(20, colx).Value = cpy(1).Offset(0, N).Value

 

[Dokat]

Sorry, my fault. Try changing this line of code...

.Cells(20, colx).Value = cpy.Offset(0, N).Value

to this...

.Cells(20, colx).Value = cpy(1).Offset(0, N).Value

Thank you, it still only copied value in R20 to T20, no other changes.

 

[Rick Rothstein]

If I understand your setup correctly, I do not see how that is possible. Can you post a desensitized copy of your workbook to DropBox so I can see how the code runs against your actual data?

 

[Dokat]

If I understand your setup correctly, I do not see how that is possible. Can you post a desensitized copy of your workbook to DropBox so I can see how the code runs against your actual data?

Thank you. here is the dropbox link.

Test data.xlsm

Shared with Dropbox

www.dropbox.com

I am trying to copy values between "G20:R20" to every 14th cell. Next paste would be starting cell "T20:AE20". Hope this clarifies.

 

[Rick Rothstein]

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