Loop through two tables to create an output

[drop05]

Hello, i am trying to find a way to take these two tables I have on two sheets and create an output. In the tab called "Lead" is X amounts of codes that i am entering. In tab "PH" there is a table where to pull from using only columns B, and E.
I am trying to loop through from tab Lead from the first code, in cell A4 down to the last row, not a7 more can be added, and loop through the list in PH that many times so X, collecting the info from column B and E and putting them into the tab called "Output" starting at row 3 down, but also collecting that first code from the lead tab and putting it in the output tab as well, with column D in the output tab always being "G", then going to the next one collecting that code and the same two columns info from PH and putting it after that first pasting, and so on. I have shown an example of the output in the output tab.

Thank you in advance!

Book2
	A	B	C	D
1
2
3	Codes
4	12345
5	54312
6	98765
7	76543
8
9
Lead

Book2
	A	B	C	D	E	F
1	UserName	User ID	Level	Sign off	Type
2	Jane Doe	jd.ID	1	Prep	SO1
3	John Deer	jd1.ID	2	R1	SO2
4	James Gunn	jg.ID	3	R2	SO3
5
6
7
PH

Cell Formulas
Range	Formula
E2:E4	E2	=IFS(D2="Prep","SO1",D2="R1","SO2",D2="R2","SO3")

Book2
	A	B	C	D	E
1	Username	Type	Code	Activity
2
3	jd.ID	SO1	12345	G
4	jd1.ID	SO2	12345	G
5	jg.ID	SO3	12345	G
6	jd.ID	SO1	54312	G
7	jd1.ID	SO2	54312	G
8	jg.ID	SO3	54312	G
9	jd.ID	SO1	98765	G
10	jd1.ID	SO2	98765	G
11	jg.ID	SO3	98765	G
12	jd.ID	SO1	76543	G
13	jd1.ID	SO2	76543	G
14	jg.ID	SO3	76543	G
15
16
17
Output

I had some code i was playing around with to start it but not sure if i am on the right track and getting stuck

Dim lr&, i&, j&, k&, rngName, rngID

If shtCompID.Range("A2") = Empty Then
    MsgBox "Please enter Component Code(s)."
    Exit Sub
Else

    With shtFilteredUsers
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        rngName = .Range("A3:A" & lr)
    End With
    
    With shtCompID
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        rngID = .Range("A2:A" & lr)
    End With
        For j = 1 To UBound(rngID)
            For i = 1 To UBound(rngName)

 

[Legacy 143009]

Where does Activity: G information come from?

 

[Peter_SSs]

Where does Activity: G information come from?

column D in the output tab always being "G"

@drop05
Assuming ..

• Table on 'Lead' is called tblLead (adjust code to suit)
• Table on PH is called tblPH (adjust code to suit)
• Sheet 'Output' exists but is empty

.. then try this with a copy of your workbook.

Sub drop05()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("tblLead[Codes]").Value
  b = Range("tblPH").Value
  ReDim c(1 To UBound(a) * UBound(b), 1 To 4)
  For i = 1 To UBound(a)
    For j = 1 To UBound(b)
      k = k + 1
      c(k, 1) = b(j, 2): c(k, 2) = b(j, 5): c(k, 3) = a(i, 1): c(k, 4) = "G"
    Next j
  Next i
  With Sheets("Output").Range("A3:D3")
    .Resize(UBound(c)).Value = c
    .Offset(-2).Value = Array("Username", "Type", "Code", "Activity")
  End With
End Sub