Macro Creating Lists From Table

[keeganh]

I am trying to write a macro in order to create lists from a table of values. Please refer to the attached spreadsheet.

The my table has two columns, 'Customer' and 'Job Number' in which there are multiple job numbers for each 'Customer' as shown on sheet1.

I want to be able to create a list of each of the Job Numbers for each of the Customers to uses else where in my project these lists will be used for dynamic drop-down boxes. I want to use a VBA macro to do this.

My initially thoughts were to use a for loop with copy and paste to arrange the data as shown on sheet4 and then name the lists following this. My macro pastes the values onto sheet 3.

Here is my attempt of such a code.

Sub Test()
Dim i As Integer 'Row Counter
Dim j As Integer 'Column Counter

For j = 2 To Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
If Worksheets("Sheet1").Range("A" & i).Value = Worksheets("Sheet2").Range("A" & j).Value Then
Worksheets("Sheet1").Range("B" & i).Copy
Worksheets("Sheet3").Cells(i, j).PasteSpecial
End If
Next i
Next j

End Sub

Job Number Lists.xlsm

[Tinbendr]

Welcome to the board!

Please put all code in code tags.

Something like this?