VBA to select the next item in a data validation list

[NYEXCEL1]

I wrote a macro that creates a data validation list from a set of cells on Sheet 1. On Sheet two I would like a have Macro select each item in order on the validation list and paste into every fourth cell. For example, if my Data Validation list on Sheet 1 contains three items: Comp. A, COmp. B, Comp. C On Sheet 2, I would like to see Comp. A in cell K1, Comp. B in cell O1, Comp. C in cell S1. I have seen a few codes but nothing that works effectively. Thanks in advance

 

[iggydarsa]

Assuming your list in Sheet 1 starts from A1 and assuming you will start displaying it on Sheet2 starting from column K:

Sub Transfer()
    Dim lr As Long: lr = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim rng As Range: Set rng = Worksheets("Sheet2").Range("K1")
    For r = 1 To lr
        rng.Value = Worksheets("Sheet1").Range("A" & r).Value
        Set rng = rng.Offset(0, 4)
    Next r
End Sub

 

[NYEXCEL1]

The list starts at A5. Also can you add a line to "control shift down" to include anything on the list. When I do the marco recorder it ends at a cell number and in the future that list may grow in size.Thanks

 

[iggydarsa]

1- This code will iterate through all the items in column A of sheet1, no matter have many records you enter.
2- if it starts from 5 then change to:

Sub Transfer()
    Dim lr As Long: lr = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim rng As Range: Set rng = Worksheets("Sheet2").Range("K1")
    For r = 5 To lr
        rng.Value = Worksheets("Sheet1").Range("A" & r).Value
        Set rng = rng.Offset(0, 4)
    Next r
End Sub

 

[NYEXCEL1]

Thank you