VBA. Return values offset loop issues

[nhbcai]

Hi all,

I have a large data set and would like to have certain values returned from that set in a separate sheet. The values (Names) occur In row D and are 86 lines apart i.e the first occurs in D17 the following D103 the on after D189 and so on. I would like vba that would loop through until the end of row returning those specific values. I fiddled around but have had no luck. Thanks in advance for your help.

 

[littleiitin]

Try this: I have used sheet2 as source file and sheet3 as destination file. change the name of sheet names according to you and try

Sub CopyPaste()
Dim i As Long
Sheet3.Range("A:A").ClearContents
Sheet2.Range("D17").Copy Sheet3.Range("A1")
For Each rngCell In Sheet2.Range("D17:D" & Sheet2.Range("D" & Rows.Count).End(xlUp).Row)
    i = i + 1
    If i = 86 Then
     rngCell.Offset(1).Copy Sheet3.Range("A" & Sheet3.Range("A" & Rows.Count).End(xlUp).Row + 1)
    i = 0
    End If
Next
End Sub

 

[Peter_SSs]

What is in column (not row) D between the Names? That is, what is in
D18:D102
D104:D188
etc