Copy every 10th cell in columns A and C to a new worksheet

[bradyj7]

Hi there,

I have values in two columns A and C. I need to write a macro to copy the values in every 10th cell in both columns to a new worksheet. The first value in column A that I would like to copy is in row 29. So the subsequent values are in rows 39,49,59 etc. In column c, the first value is in row 35. So the subsequent values are in rows 45,55,65 etc. The number of rows is varies.

Thanks in advance for the help.

 

[VoG]

Try

Sub Copy10()
Dim LR As Long, i As Long, ws As Worksheet
Set ws = ActiveSheet
Worksheets.Add
With ws
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 29 To LR Step 10
        .Range("A" & i).Copy Destination = Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 35 To LR Step 10
        .Range("A" & i).Copy Destination = Range("B" & Rows.Count).End(xlUp).Offset(1)
    Next i
End With
End Sub

 

[bradyj7]

Hi thanks for the reply,

I'm getting this error 'copy method or range class failed" on this line

.Range("A" & i).Copy Destination = Range("A" & Rows.Count).End(xlUp).Offset(1)

thank you

 

[VoG]

Sorry:

Sub Copy10()
Dim LR As Long, i As Long, ws As Worksheet
Set ws = ActiveSheet
Worksheets.Add
With ws
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 29 To LR Step 10
        .Range("A" & i).Copy Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 35 To LR Step 10
        .Range("A" & i).Copy Destination:=Range("B" & Rows.Count).End(xlUp).Offset(1)
    Next i
End With
End Sub

 

[bradyj7]

Thank you