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

msew1234

New Member
Joined
Mar 11, 2018
Messages
7
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
you have to write a very simple macro

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

have you used macros before ?
 
Upvote 0
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
 
Upvote 0
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

 
Upvote 0
[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]
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,129
Messages
6,170,278
Members
452,317
Latest member
henrybsimple

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top