Challenging; Creating an automatic list depending on two tables...

[lost_my_SQL]

If I have a list of job titles [JBTBL], and a list of contract years [POPTBL]; I need to copy the list of job titles for as many times as I have years, lets call this the DetailTable. Then if I change/delete/add a job title from the job title table or add or delete a contract year from the POPTBL, i need the DetailTable to automatically reflect to these changes. This would save me hours of copy and pasting and re-working formulas.

**I refer to them as tables because they contain a lot more information but those are easy vlookups after i get the duplication figured out.

Hopefully my explanation makes sense.

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]JBTBL[/TD]
[/TR]
[TR]
[TD]PLUMBER[/TD]
[/TR]
[TR]
[TD]CARPENTER[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]POPTBL[/TD]
[/TR]
[TR]
[TD]YEAR 1[/TD]
[/TR]
[TR]
[TD]YEAR 2[/TD]
[/TR]
</tbody>[/TABLE]

Auto-Details Table
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]POP[/TD]
[TD]JOB[/TD]
[/TR]
[TR]
[TD]YEAR 1[/TD]
[TD]PLUMBER[/TD]
[/TR]
[TR]
[TD]YEAR 1[/TD]
[TD]CARPENTER[/TD]
[/TR]
[TR]
[TD]YEAR 2[/TD]
[TD]PLUMBER[/TD]
[/TR]
[TR]
[TD]YEAR 2[/TD]
[TD]CARPENTER[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

I used this layout you will need to adjust the code to match yours.

Book1
	A	B	C	D	E	F
1	JBTBL		POPTBL		POP	Job
2	PLUMBER		YEAR 1		YEAR 1	PLUMBER
3	CARPENTER		YEAR 2		YEAR 1	CARPENTER
4					YEAR 2	PLUMBER
5					YEAR 2	CARPENTER
Sheet1

Try

Sub pop()
Dim lrj As Long
Dim lry As Long
Dim lr As Long
Dim jcount As Long
Dim ycount As Long

lrj = Cells(Rows.Count, "A").End(xlUp).Row 'change to column with your JBTBL
lry = Cells(Rows.Count, "C").End(xlUp).Row 'change to column with your POPTBL
lre = Cells(Rows.Count, "E").End(xlUp).Row 'change to column with your created table is
lrf = Cells(Rows.Count, "F").End(xlUp).Row 'change to column with your created table is
jcount = lrj - 1 'assuming headers are in row 1 if not change the 1 to where the headers are
ycount = lry - 1 'assuming headers are in row 1 if not change the 1 to where the headers are
'clear table if populated
If lrf > 1 Then
    Range("E2:F" & lrf).ClearContents
    lre = Cells(Rows.Count, "E").End(xlUp).Row 'change to column with your created table is
    lrf = Cells(Rows.Count, "F").End(xlUp).Row 'change to column with your created table is
End If
For c = 1 To ycount
    Range("A2:A" & lrj).Copy Range("F" & lrf + 1)
    lrf = Cells(Rows.Count, "F").End(xlUp).Row 'change to column with your created table is
Next c
For x = 2 To lry
    For p = 1 To jcount
        Range("C" & x).Copy Range("E" & lre + 1)
        lre = Cells(Rows.Count, "E").End(xlUp).Row 'change to column with your created table is
    Next p
Next x

End Sub

If you want this could be changed to a change event so it runs automatically.