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

jconda

New Member
Joined
Feb 16, 2024
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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:

Consolidate Menu.png


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:

VBA 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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,224,244
Messages
6,177,391
Members
452,773
Latest member
D P

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