Very simple question probably, apologies

New Contributor

So basically I am having a problem that I feel like there is a very simple solution to but I cant work it out.

On my workbook I have two sheets, one with a range of data with 30 values spanning A1 to A30. Picture below:




I want to create another page where I bring the same data across, however there will now be 3 spaces between each value, however, whenever I do this, and leave the three spaces after the first value and try to use the handle to fill down, it takes every third value, like this:




Is there a way I can make sheet 2 cell A5 (currently displaying sheet 1 A5 (12.5) after filling down) display the data from sheet 1 A2 (8) by filling downwards from sheet 2 A1, rather than having to manually update every third cell to the correct reference?


Apologies if this is not very clear.


2 Replies
best response confirmed by Hans Vogelaar (MVP)


Sub values()

Dim i As Long
Dim j As Long
Dim k As Long

i = Range("A" & Rows.Count).End(xlUp).Row
k = 1

For j = 1 To i
Cells(k, 3).Value = Cells(j, 1).Value
k = k + 4

Next j

End Sub

Maybe with these lines of code. In the attached file you can click the button in cell F2 to run the macro.

Perfect, works ideally, thank you!