Consolidate data from multiple sheets based on values selected in dependent list boxes

[jconda]

Dear all,

I hope someone can help me, as I am quite new to VBA.

I have Excel Workbook that contains 8 Sheets (data from different sources) and all these sheets have the same column names - JPCode and ZoneName. Example:

I want to consolidate data from the selected Sheets (Suppliers list box), but when I select certain Sheet, I need to populate Columns list box with the Column Names that are taken from the selected Sheets. Right now there will be only 2 columns - JPCode and ZoneName. However, I want to have it dynamic - if I add a new column in the sheet and this particular sheet is selected, then this new column will appear in the Columns list box. The same goes with Values. I want Values list box to populate with selectable unique values based on my selection in Suppliers (Sheets) and Columns (Column Name) list boxes. Based on these selected criteria / filters and when I press OK button - the data is consolidated in "Consolidate" sheet.

For example: I select Sheet1 and Sheet2 under Suppliers list box, then I have two options displayed JPCode and ZoneName under Columns list box (all possible column names). I select ZoneName in Columns list box and then all unique values (unique data under this particular column) are shown in Values list box - I can select Egypt, Jordan and USA. After hitting OK button and based on this selection my data will be consolidated - JPCode and ZoneName consolidation.

I already created macro to consolidate based on Sheets (Suppliers), but I am struggling to make these list boxes dependent on each other. In simple words, adding two more additional filters for my consolidation - based on Columns and Values list boxes.

Can anyone help me with this please?

My existing code:

Private Sub ChkAll_Click()

Dim i As Integer

For i = 1 To Me.LstSupplier.ListCount

Me.LstSupplier.Selected(i - 1) = Me.ChkAll.Value

Next

End Sub

Private Sub CmdOK_Click()

Dim shC As Worksheet
Dim shD As Worksheet
Dim i As Integer
Dim RngD As Range
Dim RngC As Range


Set shC = ThisWorkbook.Worksheets("Consolidate")

shC.Range("A2:B1000000").Clear

For i = 1 To Me.LstSupplier.ListCount

    If Me.LstSupplier.Selected(i - 1) = True Then
    
    Set shD = Worksheets("" & Me.LstSupplier.List(i - 1, 0) & "")
    Set RngD = shD.Range("A2:B" & WorksheetFunction.CountA(shD.Range("A:A")))
    Set RngC = shC.Range("A" & WorksheetFunction.CountA(shC.Range("A:A")) + 1)
    
    RngD.Copy RngC
    
    End If
    
Next

End Sub

Private Sub LstSupplier_Change()

Dim i As Integer
Dim j As Integer

For i = 1 To Me.LstSupplier.ListCount

If Me.LstSupplier.Selected(i - 1) = True Then

j = j + 1

End If

Next

If j > 0 Then
Me.CmdOK.Enabled = True
Else
Me.CmdOK.Enabled = False
End If

End Sub

Private Sub UserForm_Initialize()

Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets

If (sh.Name <> "Consolidate") Then Me.LstSupplier.AddItem sh.Name

Next

End Sub

Thank you in advance for your help!