Auto Populate (Specific Purpose): Fill small tables with fixed header titles with data from organized cells

[msew1234]

Hello,

I hope you are all well,

Problem:
I have this table (for example):

I want to fill above source data into small forms / pattern / tables like this picture below, so I can be able to print them like that,

What would be the solution, as I tried auto fill and it did not help?

Thanks

 

[oldbrewer]

sum= 1
for j=1 to 16 step 5
sum=sum+1

allows you to pick off the data and use J, J+1, j+2, j+3 to locate the data in your desired table layout

alternatively
for j=1 to 16 step 5
for k=1 to 5

allows you to pick off each item in the top table
eg when j=1 and k=4 cells(j,k) refers to 20 in the age column


obviously you will need a next j in your code

 

[Fluff]

Cross posted https://superuser.com/questions/130...-header-titles-with-data-from-different-cells

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[msew1234]

sum= 1
for j=1 to 16 step 5
sum=sum+1

allows you to pick off the data and use J, J+1, j+2, j+3 to locate the data in your desired table layout

alternatively
for j=1 to 16 step 5
for k=1 to 5

allows you to pick off each item in the top table
eg when j=1 and k=4 cells(j,k) refers to 20 in the age column


obviously you will need a next j in your code

Thanks for replying, but I do not know how to integrate this code into excel format (cells &rows & columns )
Is there any post/link for clarification??

Best Regards,

 

[oldbrewer]

you have to write a very simple macro

cell G3 is the same as cells(3,7)

have you used macros before ?

 

[msew1234]

you have to write a very simple macro

cell G3 is the same as cells(3,7)

have you used macros before ?

Honestly, I recorded a simple macro before, not wrote one,
but I will give a try, then I will show ,,

Thanks

 

[msew1234]

This is Macro I have just done,

How can I loop over the cell in Sheet 2 (Source Data )?
Sub Test2()
Dim j As Integer
Dim i As Integer
Dim k As Integer
Dim l As Integer






Sheets("Sheet3").Select


For j = 2 To 17 Step 5
k = 1

'Name'
Cells(j, k).Value = "=Sheet2!R[-4]C"
k = k + 1
'Degree'
Cells(j, k).Value = "=Sheet2!C3"

Next j


For i = 4 To 19 Step 5
l = 1
'Percentage'
Cells(i, l).Value = "=Sheet2!E3"
l = l + 1
'Class'
Cells(i, l).Value = "=Sheet2!B3"
Next i




End Sub

 

[oldbrewer]

For j = 2 To 17 Step 5
k = 1

'Name'
Cells(j, k).Value = "=Sheet2!R[-4]C"
k = k + 1
'Degree'
Cells(j, k).Value = "=Sheet2!C3"

Next j

Cells(j, k).Value = "=Sheet2!R[-4]C"
why are you adding .value and why RC notation

 

[oldbrewer]

[TABLE="width: 914"]
<colgroup><col><col><col span="2"><col><col span="8"></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]class[/TD]
[TD]degree[/TD]
[TD]age[/TD]
[TD]percentage[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]5-E[/TD]
[TD]5[/TD]
[TD]20[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]6-A[/TD]
[TD]10[/TD]
[TD]22[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2-A[/TD]
[TD]20[/TD]
[TD]23[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]5-E[/TD]
[TD]30[/TD]
[TD]24[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]4-A[/TD]
[TD]40[/TD]
[TD]22[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]DEGREE[/TD]
[TD][/TD]
[TD]ROW 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PERCENTAGE[/TD]
[TD]CLASS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]THIS SIMPLE EASY TO UNDERSTAND MACRO[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]5-E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]MAKES THE LAYOUT YOU WANT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]DEGREE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sub Macro4()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PERCENTAGE[/TD]
[TD]CLASS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]' Macro4 Macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]6-A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]' Macro recorded 12/03/2018 by bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]DEGREE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PERCENTAGE[/TD]
[TD]CLASS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] J = 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]2-A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] For N = 10 To 30 Step 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Cells(N, 1) = "NAME"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]DEGREE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Cells(N, 2) = "DEGREE"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Cells(N + 2, 1) = "PERCENTAGE"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PERCENTAGE[/TD]
[TD]CLASS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cells(N + 2, 2) = "CLASS"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]5-E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]J = J + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cells(N + 1, 1) = Cells(J, 1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]DEGREE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cells(N + 1, 2) = Cells(J, 3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cells(N + 3, 1) = Cells(J, 5)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PERCENTAGE[/TD]
[TD]CLASS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cells(N + 3, 2) = Cells(J, 2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70[/TD]
[TD]4-A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Next N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[msew1234]

For j = 2 To 17 Step 5
k = 1

'Name'
Cells(j, k).Value = "=Sheet2!R[-4]C"
k = k + 1
'Degree'
Cells(j, k).Value = "=Sheet2!C3"

Next j

Cells(j, k).Value = "=Sheet2!R[-4]C"
why are you adding .value and why RC notation

Sorry for bothering, I hope to be this is the last post,

Because, the tables are in 2 different sheets (Sheet1, Sheet2) and I want them to be changeable (meaning that if I changed the degree cell in Sheet 1 it changes in sheet 2 related cell)...

I have done this code too:

Sub Test2()


j = 1
m = 2
For k = 2 To 6 Step 1


'Name'
Worksheets("Sheet3").Cells(m, j) = Worksheets("Sheet2").Cells(k, j)
'Percentage'
Worksheets("Sheet3").Cells(m + 2, j) = Worksheets("Sheet2").Cells(k, 5)
m = m + 5
Next k

End Sub