SOLVED

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:

 

sconboy_0-1669994218746.png

 

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:

 

sconboy_1-1669994366160.png

 

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)
Solution

@sconboy 

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!