Extracting data from one worksheet to a new list

[Richiejjj]

Hi

I am working with someones worksheet which contains a 'template' they enter data into. The first 'template' is in a2:r30 and is then repeated after a few spaces going down the workbook.

I need to extract some of the cells data so I can analyse it. They are consistent in naming and location as they use the template.

Is there a way I can link to it and drag down the difference in cell numbers to extract the data?

Thanks

 

[Lukasier]

But you need to specify where on the list do you want them. I have no idea what is in cell C3. You need to tell me that.

Because that range you mentioned doesnt match the one you posted before on the picture.

 

[Richiejjj]

The total data set sits in the range c3:K18 but i only need the data from the cells below in a single row:

C3
C4
C5
H3
H4
H5
C8:E17
F8:F16
K4:K18

the next row of the table would have the data from

C3+19
C4+19
C5+19
H3+19
H4+19
H5+19
(C8:E17)+19
(F8:F16)+19
(K4:K18)+19

Etc

I dont mind what the row headings are as ill be manipulating the data in query when i have it in a table.

 

[Lukasier]

Sub List()

Dim oldSheet As Worksheet
Dim newSheet As Worksheet
Dim Cnt As Long
Dim j, i As Long
Dim cell As Range

Set oldSheet = ActiveSheet
Set newSheet = Worksheets.Add

Cnt = 0
j = 0
i = 2

Do While oldSheet.Range("C" & 3 + Cnt) <> ""
newSheet.Cells(i, 1) = oldSheet.Range("C" & 3 + Cnt)
newSheet.Cells(i, 2) = oldSheet.Range("C" & 4 + Cnt)
newSheet.Cells(i, 3) = oldSheet.Range("C" & 5 + Cnt)
newSheet.Cells(i, 4) = oldSheet.Range("H" & 3 + Cnt)
newSheet.Cells(i, 5) = oldSheet.Range("H" & 4 + Cnt)
newSheet.Cells(i, 6) = oldSheet.Range("H" & 5 + Cnt)
For Each cell In oldSheet.Range("C" & 8 + Cnt, "E" & 17 + Cnt)
newSheet.Cells(i, 7 + j) = cell.Value
j = j + 1
Next cell
j = 0
For Each cell In oldSheet.Range("F" & 8 + Cnt, "F" & 16 + Cnt)
newSheet.Cells(i, 38 + j) = cell.Value
j = j + 1
Next cell
j = 0
For Each cell In oldSheet.Range("K" & 4 + Cnt, "K" & 18 + Cnt)
newSheet.Cells(i, 53 + j) = cell.Value
j = j + 1
Next cell
Cnt = Cnt + 19
i = i + 1
j = 0
Loop

End Sub

 

[Richiejjj]

Thanks for this, trying to understand it as there are a couple of data areas not captured.

Simply?:

CNT is the dropdown to the next batch of cells?
i is the list column row?
j is the list row location?

It won't do anything if C3 is empty or C3 +19

Should it be returning everything for all cells in the template range, there are unique items in each cell within each of C8:E17?