show data for each sheet in listbox and merge based on combobox and optionbutton

A

Ali M

Active Member
Joined
Oct 10, 2021
Messages
348
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
hi
I have about three sheets and based on column C it will repeat data across multiple sheets or just the same sheet so what I want when select specific sheet from combobox . it will show all of data is relating the selected sheet based on combobox and if I select specific sheet and select optionbutton(collection) then should merge duplicate items based on column C and only sum values column H .about column I should calculate average price because when repeat the item based on column C somtimes changes the price and about column J = column H x column I and if I select specific sheet from combobox and select optionbutton (not collection) then should cancel merging data are relating for selected sheet based on combobox and if the combobox1 is empty and selected optionbuttons then when run user form should merge all od data across multiple sheets with considering calculate average price

merge in userform.xlsm
ABCDEFGHIJ
1DATENAMECODEINVOICEBDTPOGQTYPRTOT
203/03/2020AL1CA-1INV1BD1TP1OG1120.00220.00 KWD26,400.00 KWD
304/03/2020AL2CA-2INV2BD2TP2OG2220.00250.00 KWD55,000.00 KWD
405/03/2020AL3CA-3INV3BD3TP3OG3320.00340.00 KWD108,800.00 KWD
506/03/2020AL4CA-4INV4BD4TP4OG4420.00390.00 KWD163,800.00 KWD
607/03/2020AL5CA-5INV5BD5TP5OG5520.00450.00 KWD234,000.00 KWD
708/03/2020AL6CA-6INV6BD6TP6OG6620.00510.00 KWD316,200.00 KWD
809/03/2020AL7CA-7INV7BD7TP7OG7720.00570.00 KWD410,400.00 KWD
910/03/2020AL8CA-8INV8BD8TP8OG8820.00630.00 KWD516,600.00 KWD
1011/03/2020AL9CA-9INV9BD9TP9OG9920.00690.00 KWD634,800.00 KWD
1112/03/2020AL10CA-10INV10BD10TP10OG101,020.00750.00 KWD765,000.00 KWD
1213/03/2020AL11CA-11INV11BD11TP11OG111,120.00810.00 KWD907,200.00 KWD
1314/03/2020AL12CA-12INV12BD12TP12OG121,220.00870.00 KWD1,061,400.00 KWD
1415/03/2020AL13CA-13INV13BD13TP13OG131,320.00930.00 KWD1,227,600.00 KWD
1516/03/2020AL14CA-14INV14BD14TP14OG141,420.00990.00 KWD1,405,800.00 KWD
1617/03/2020AL15CA-15INV15BD15TP15OG151,520.001,050.00 KWD1,596,000.00 KWD
1718/03/2020AL16CA-16INV16BD16TP16OG161,620.001,110.00 KWD1,798,200.00 KWD
1819/03/2020AL17CA-17INV17BD17TP17OG171,720.001,170.00 KWD2,012,400.00 KWD
1920/03/2020AL18CA-18INV18BD18TP18OG181,820.001,230.00 KWD2,238,600.00 KWD
2021/03/2020AL19CA-19INV19BD19TP19OG191,920.001,290.00 KWD2,476,800.00 KWD
2122/03/2020AL20CA-20INV20BD20TP20OG202,020.001,350.00 KWD2,727,000.00 KWD
2223/03/2020AL21CA-21INV21BD21TP21OG212,120.001,410.00 KWD2,989,200.00 KWD
2324/03/2020AL22CA-22INV22BD22TP22OG222,220.001,470.00 KWD3,263,400.00 KWD
2425/03/2020AL23CA-23INV23BD23TP23OG232,320.001,530.00 KWD3,549,600.00 KWD
2526/03/2020AL24CA-24INV24BD24TP24OG242,420.001,590.00 KWD3,847,800.00 KWD
2627/03/2020AL25CA-25INV25BD25TP25OG252,520.001,650.00 KWD4,158,000.00 KWD
2728/03/2020AL26CA-26INV26BD26TP26OG262,620.001,710.00 KWD4,480,200.00 KWD
2829/03/2020AL27CA-27INV27BD27TP27OG272,720.001,770.00 KWD4,814,400.00 KWD
2930/03/2020AL28CA-28INV28BD28TP28OG282,820.001,830.00 KWD5,160,600.00 KWD
3031/03/2020AL29CA-29INV29BD29TP29OG292,920.001,890.00 KWD5,518,800.00 KWD
3101/04/2020AL30CA-30INV30BD30TP30OG303,020.001,950.00 KWD5,889,000.00 KWD
3202/04/2020AL31CA-31INV31BD31TP31OG313,120.002,010.00 KWD6,271,200.00 KWD
3303/04/2020AL32CA-32INV32BD32TP32OG323,220.002,070.00 KWD6,665,400.00 KWD
3404/04/2020AL33CA-33INV33BD33TP33OG333,320.002,130.00 KWD7,071,600.00 KWD
3505/04/2020AL34CA-34INV34BD34TP34OG343,420.002,190.00 KWD7,489,800.00 KWD
3606/04/2020AL35CA-35INV35BD35TP35OG353,520.002,250.00 KWD7,920,000.00 KWD
3707/04/2020AL36CA-36INV36BD36TP36OG363,620.002,310.00 KWD8,362,200.00 KWD
3808/04/2020AL37CA-37INV37BD37TP37OG373,720.002,370.00 KWD8,816,400.00 KWD
3909/04/2020AL38CA-38INV38BD38TP38OG383,820.002,430.00 KWD9,282,600.00 KWD
4010/04/2020AL39CA-39INV39BD39TP39OG393,920.002,490.00 KWD9,760,800.00 KWD
4111/04/2020AL40CA-40INV40BD40TP40OG404,020.002,550.00 KWD10,251,000.00 KWD
4212/04/2020AL1CA-1INV1BD1TP1OG1100.00210.00 KWD21,000.00 KWD
4313/04/2020AL2CA-2INV2BD2TP2OG250.00240.00 KWD12,000.00 KWD
4414/04/2020AL3CA-3INV3BD3TP3OG3200.00335.00 KWD67,000.00 KWD
4515/04/2020AL4CA-4INV4BD4TP4OG4120.00380.00 KWD45,600.00 KWD
4616/04/2020AL5CA-5INV5BD5TP5OG550.00445.00 KWD22,250.00 KWD
SH1
Cell Formulas
RangeFormula
J2:J46J2=H2*I2




merge in userform.xlsm
ABCDEFGHIJ
1DATENAMECODEINVOICEBDTPOGQTYPRTOT
203/04/2020AL1CA-1INV1BD1TP1OG1100.00220.00 KWD22,000.00 KWD
304/04/2020AL2CA-2INV2BD2TP2OG2200.00250.00 KWD50,000.00 KWD
405/04/2020AL3CA-3INV3BD3TP3OG3300.00340.00 KWD102,000.00 KWD
506/04/2020AL4CA-4INV4BD4TP4OG4400.00390.00 KWD156,000.00 KWD
607/04/2020AL5CA-5INV5BD5TP5OG5500.00450.00 KWD225,000.00 KWD
708/04/2020AL6CA-6INV6BD6TP6OG6600.00510.00 KWD306,000.00 KWD
809/04/2020AL7CA-7INV7BD7TP7OG7700.00570.00 KWD399,000.00 KWD
910/04/2020AL8CA-8INV8BD8TP8OG8800.00630.00 KWD504,000.00 KWD
1011/04/2020AL9CA-9INV9BD9TP9OG9900.00690.00 KWD621,000.00 KWD
1112/04/2020AL10CA-10INV10BD10TP10OG101,000.00750.00 KWD750,000.00 KWD
1213/04/2020AL11CA-11INV11BD11TP11OG111,100.00810.00 KWD891,000.00 KWD
1314/04/2020AL12CA-12INV12BD12TP12OG121,200.00870.00 KWD1,044,000.00 KWD
1415/04/2020AL13CA-13INV13BD13TP13OG131,300.00930.00 KWD1,209,000.00 KWD
1516/04/2020AL14CA-14INV14BD14TP14OG141,400.00990.00 KWD1,386,000.00 KWD
1617/04/2020AL15CA-15INV15BD15TP15OG151,500.001,050.00 KWD1,575,000.00 KWD
1718/04/2020AL16CA-16INV16BD16TP16OG161,600.001,110.00 KWD1,776,000.00 KWD
1819/04/2020AL17CA-17INV17BD17TP17OG171,700.001,170.00 KWD1,989,000.00 KWD
1920/04/2020AL18CA-18INV18BD18TP18OG181,800.001,230.00 KWD2,214,000.00 KWD
2021/04/2020AL19CA-19INV19BD19TP19OG191,900.001,290.00 KWD2,451,000.00 KWD
2122/04/2020AL20CA-20INV20BD20TP20OG202,000.001,350.00 KWD2,700,000.00 KWD
2223/04/2020AL21CA-21INV21BD21TP21OG212,100.001,410.00 KWD2,961,000.00 KWD
2324/04/2020AL22CA-22INV22BD22TP22OG222,200.001,470.00 KWD3,234,000.00 KWD
2425/04/2020AL23CA-23INV23BD23TP23OG232,300.001,530.00 KWD3,519,000.00 KWD
2526/04/2020AL24CA-24INV24BD24TP24OG242,400.001,590.00 KWD3,816,000.00 KWD
2627/04/2020AL25CA-25INV25BD25TP25OG252,500.001,650.00 KWD4,125,000.00 KWD
2728/04/2020AL26CA-26INV26BD26TP26OG262,600.001,710.00 KWD4,446,000.00 KWD
2829/04/2020AL27CA-27INV27BD27TP27OG272,700.001,770.00 KWD4,779,000.00 KWD
2930/04/2020AL28CA-28INV28BD28TP28OG282,800.001,830.00 KWD5,124,000.00 KWD
3001/05/2020AL29CA-29INV29BD29TP29OG292,900.001,890.00 KWD5,481,000.00 KWD
3102/05/2020AL30CA-30INV30BD30TP30OG303,000.001,950.00 KWD5,850,000.00 KWD
3203/05/2020AL31CA-31INV31BD31TP31OG313,100.002,010.00 KWD6,231,000.00 KWD
3304/05/2020AL32CA-32INV32BD32TP32OG323,200.002,070.00 KWD6,624,000.00 KWD
3405/05/2020AL33CA-33INV33BD33TP33OG333,300.002,130.00 KWD7,029,000.00 KWD
3506/05/2020AL34CA-34INV34BD34TP34OG343,400.002,190.00 KWD7,446,000.00 KWD
3607/05/2020AL35CA-35INV35BD35TP35OG353,500.002,250.00 KWD7,875,000.00 KWD
3708/05/2020AL36CA-36INV36BD36TP36OG363,620.002,310.00 KWD8,362,200.00 KWD
3809/05/2020AL37CA-37INV37BD37TP37OG373,720.002,370.00 KWD8,816,400.00 KWD
3910/05/2020AL38CA-38INV38BD38TP38OG383,820.002,430.00 KWD9,282,600.00 KWD
4011/05/2020AL39CA-39INV39BD39TP39OG393,920.002,490.00 KWD9,760,800.00 KWD
4112/05/2020AL40CA-40INV40BD40TP40OG404,020.002,550.00 KWD10,251,000.00 KWD
4213/05/2020AL38CA-38INV38BD38TP38OG383,800.002,500.00 KWD9,500,000.00 KWD
4314/05/2020AL39CA-39INV39BD39TP39OG393,900.002,600.00 KWD10,140,000.00 KWD
4415/05/2020AL40CA-40INV40BD40TP40OG404,000.002,700.00 KWD10,800,000.00 KWD
4516/05/2020AL1CA-1INV1BD1TP1OG150.00215.00 KWD10,750.00 KWD
SH2
Cell Formulas
RangeFormula
J2:J45J2=H2*I2




merge in userform.xlsm
ABCDEFGHIJ
1DATENAMECODEINVOICEBDTPOGQTYPRTOT
203/05/2020AL1CA-1INV1BD1TP1OG1150.00220.00 KWD33,000.00 KWD
304/05/2020AL2CA-2INV2BD2TP2OG2150.00250.00 KWD37,500.00 KWD
405/05/2020AL3CA-3INV3BD3TP3OG3200.00340.00 KWD68,000.00 KWD
506/05/2020AL4CA-4INV4BD4TP4OG4200.00390.00 KWD78,000.00 KWD
607/05/2020AL5CA-5INV5BD5TP5OG5250.00450.00 KWD112,500.00 KWD
708/05/2020AL6CA-6INV6BD6TP6OG6300.00510.00 KWD153,000.00 KWD
809/05/2020AL7CA-7INV7BD7TP7OG7120.00570.00 KWD68,400.00 KWD
910/05/2020AL8CA-8INV8BD8TP8OG8400.00630.00 KWD252,000.00 KWD
1011/05/2020AL9CA-9INV9BD9TP9OG9850.00690.00 KWD586,500.00 KWD
1112/05/2020AL10CA-10INV10BD10TP10OG101,100.00750.00 KWD825,000.00 KWD
1213/05/2020AL11CA-11INV11BD11TP11OG11100.00810.00 KWD81,000.00 KWD
1314/05/2020AL12CA-12INV12BD12TP12OG12200.00870.00 KWD174,000.00 KWD
1415/05/2020AL13CA-13INV13BD13TP13OG13300.00930.00 KWD279,000.00 KWD
1516/05/2020AL14CA-14INV14BD14TP14OG14500.00990.00 KWD495,000.00 KWD
1617/05/2020AL15CA-15INV15BD15TP15OG15600.001,050.00 KWD630,000.00 KWD
1718/05/2020AL16CA-16INV16BD16TP16OG16700.001,110.00 KWD777,000.00 KWD
1819/05/2020AL17CA-17INV17BD17TP17OG17800.001,170.00 KWD936,000.00 KWD
1920/05/2020AL18CA-18INV18BD18TP18OG18150.001,230.00 KWD184,500.00 KWD
2021/05/2020AL19CA-19INV19BD19TP19OG19900.001,290.00 KWD1,161,000.00 KWD
2122/05/2020AL20CA-20INV20BD20TP20OG20150.001,350.00 KWD202,500.00 KWD
2223/05/2020AL21CA-21INV21BD21TP21OG21200.001,410.00 KWD282,000.00 KWD
2324/05/2020AL22CA-22INV22BD22TP22OG22250.001,470.00 KWD367,500.00 KWD
2425/05/2020AL23CA-23INV23BD23TP23OG23300.001,530.00 KWD459,000.00 KWD
2526/05/2020AL24CA-24INV24BD24TP24OG24100.001,590.00 KWD159,000.00 KWD
2627/05/2020AL25CA-25INV25BD25TP25OG252,500.001,650.00 KWD4,125,000.00 KWD
2728/05/2020AL26CA-26INV26BD26TP26OG262,600.001,710.00 KWD4,446,000.00 KWD
2829/05/2020AL27CA-27INV27BD27TP27OG272,700.001,770.00 KWD4,779,000.00 KWD
2930/05/2020AL28CA-28INV28BD28TP28OG282,800.001,830.00 KWD5,124,000.00 KWD
3031/05/2020AL29CA-29INV29BD29TP29OG292,900.001,890.00 KWD5,481,000.00 KWD
3101/06/2020AL30CA-30INV30BD30TP30OG303,000.001,950.00 KWD5,850,000.00 KWD
3202/06/2020AL31CA-31INV31BD31TP31OG313,100.002,010.00 KWD6,231,000.00 KWD
3303/06/2020AL32CA-32INV32BD32TP32OG323,200.002,070.00 KWD6,624,000.00 KWD
3404/06/2020AL33CA-33INV33BD33TP33OG333,300.002,130.00 KWD7,029,000.00 KWD
3505/06/2020AL34CA-34INV34BD34TP34OG343,400.002,190.00 KWD7,446,000.00 KWD
3606/06/2020AL35CA-35INV35BD35TP35OG353,500.002,250.00 KWD7,875,000.00 KWD
3707/06/2020AL36CA-36INV36BD36TP36OG363,620.002,310.00 KWD8,362,200.00 KWD
3808/06/2020AL37CA-37INV37BD37TP37OG373,720.002,370.00 KWD8,816,400.00 KWD
3909/06/2020AL38CA-38INV38BD38TP38OG383,820.002,430.00 KWD9,282,600.00 KWD
4010/06/2020AL39CA-39INV39BD39TP39OG393,920.002,490.00 KWD9,760,800.00 KWD
4111/06/2020AL40CA-40INV40BD40TP40OG404,020.002,550.00 KWD10,251,000.00 KWD
4212/06/2020AL38CA-38INV38BD38TP38OG383,800.002,500.00 KWD9,500,000.00 KWD
4313/06/2020AL39CA-39INV39BD39TP39OG393,900.002,600.00 KWD10,140,000.00 KWD
4414/06/2020AL40CA-40INV40BD40TP40OG404,000.002,700.00 KWD10,800,000.00 KWD
4515/06/2020AL40CA-40INV40BD40TP40OG405,000.002,800.00 KWD14,000,000.00 KWD
4616/06/2020AL1CA-1INV1BD1TP1OG1150.00225.00 KWD33,750.00 KWD
SH3
Cell Formulas
RangeFormula
J2:J46J2=H2*I2



condition 1when run userform sholud merge all of data based on column C and calculate price average
cs1.PNG


condition2 when select specific sheet should show the data without merge

cs3.PNG



condition 3
when select sheet and option collection
CS5.PNG


finally If I select from combobox specific sheet and option (not collection it will return condition2

NOTE: the highlight rows by red . they are repeated data and should merge based on column C and the highlighted cells by green should calculate price average . you can see the first five rows in listbox how should mrege and how should calculate with considering somtimes the items based on column C repeat for all sheets or the same sheet
I hope coverd all od detailes
thanks in advance
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Do the following:
1. For the headers to appear in the listbox create a sheet with the name "Temp"
2. Format columns I and J of the Temp sheet with: #, ##0.00 "KWD"
3. Add a Commandbutton in your userform.
4. Put all the following code in your userform.
5. For operation, select the option, the sheet and press the button.


VBA Code: 
Option Explicit

Dim lr As Long, sht As Worksheet

Private Sub CommandButton1_Click()
  Dim sh As Worksheet
  Dim dic As Object
  Dim i As Long
  
  Set dic = CreateObject("Scripting.dictionary")
  sht.Range("A1").Value = "ID"
  sht.Range("A:A").NumberFormat = "General"
  
  If ComboBox1.Value = "" Then
    ReDim b(1 To lr, 1 To 11)
    For i = 0 To ComboBox1.ListCount - 1
      Set sh = Sheets(ComboBox1.List(i))
      Call fillarray(dic, sh, b)
    Next
  Else
    If OptionButton1 Then
      Set sh = Sheets(ComboBox1.Value)
      ReDim b(1 To sh.Range("A" & Rows.Count).End(3).Row - 1, 1 To 11)
      Call fillarray(dic, sh, b)
    Else
      b = Sheets(ComboBox1.Value).Range("A2:J" & Sheets(ComboBox1.Value).Range("A" & Rows.Count).End(3).Row).Value
      sht.Range("A1").Value = "DATE"
    End If
  End If
  sht.Range("A2:J" & Rows.Count).ClearContents
  sht.Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  ListBox1.RowSource = sht.Name & "!" & "A2:J" & sht.Range("A" & Rows.Count).End(3).Row
End Sub

Sub fillarray(dic, sh, b)
  Dim a() As Variant
  Dim j As Long, k As Long, m As Long, n As Long

  Erase a
  a = sh.Range("A2:J" & sh.Range("A" & Rows.Count).End(3).Row).Value
  For j = 1 To UBound(a, 1)
    If Not dic.exists(a(j, 2)) Then
      m = m + 1
      n = 1
    Else
      m = Split(dic(a(j, 2)), "|")(0)
      n = Split(dic(a(j, 2)), "|")(1) + 1
    End If
    dic(a(j, 2)) = m & "|" & n
    For k = 1 To UBound(a, 2)
      Select Case k
        Case 1
          b(m, k) = m
        Case 2 To 7
          b(m, k) = a(j, k)
        Case 8
          b(m, k) = b(m, k) + a(j, k)
        Case 9
          b(m, 11) = b(m, 11) + a(j, k)
          b(m, k) = b(m, 11) / n
        Case 10
          b(m, k) = b(m, 8) * b(m, 9)
      End Select
    Next
  Next
End Sub

Private Sub UserForm_Initialize()
  ComboBox1.AddItem "SH1"
  ComboBox1.AddItem "SH2"
  ComboBox1.AddItem "SH3"
  
  'After loading the names of the sheets in the combobox, put this code.
  Dim i As Long
  For i = 0 To ComboBox1.ListCount - 1
    lr = lr + Sheets(ComboBox1.List(i)).Range("A" & Rows.Count).End(3).Row - 1
  Next
  ListBox1.ColumnHeads = True
  Set sht = Sheets("Temp")
  sht.Range("A1:J1").Value = Sheets(ComboBox1.List(1)).Range("A1:J1").Value
End Sub
 
Upvote 0
wow ! huge work and time to achieve that . very impressive !:)
but the project missed somethings. I hope to be simple for you.
first as you know when the optionbuttons and combobox not selected and press command button it will merge and calculate price average . I would also when the optionbutton(not collection) is selected and combobox1 is empty then should brings all of data from all the sheet below each other of them without merge values and calculate price average (I mean just show all of data for all sheets as is existed in all sheets).
second the numberformat and currency don't show in listbox at all despite of they are existed in all the sheets .
may you mod theses requirements ,please?
 
Upvote 0
Ali M said:
I hope coverd all od detailes
Click to expand...
Ali M said:
I would also when the optionbutton(not collection) is selected and combobox1 is empty then should brings all of data from all the sheet
Click to expand...
You didn't put that option in the original post. That is why the code gets the result of option 1 (empty combo).

Ali M said:
second the numberformat and currency don't show in listbox at all despite of they are existed in all the sheets .
Click to expand...
You must put the format in the "Temp" sheet.


--------------------------------------------
However, here is the corrected and enlarged version.

VBA Code: 
Dim lr As Long, sht As Worksheet

Private Sub CommandButton1_Click()
  Dim sh As Worksheet
  Dim dic As Object
  Dim i As Long, j As Long, k As Long, m As Long
  Dim a() As Variant, b As Variant
  
  Set dic = CreateObject("Scripting.dictionary")
  sht.Range("A1").Value = "ID"
  sht.Range("A:A").NumberFormat = "General"
  sht.Range("I:J").NumberFormat = "#,##0.00 ""KWD"""
  With ComboBox1
    If .Value = "" Then
      ReDim b(1 To lr, 1 To 11)
      If OptionButton1 Then
        For i = 0 To .ListCount - 1
          Set sh = Sheets(.List(i))
          Call fillarray(dic, sh, b)
        Next
      Else
        For i = 0 To .ListCount - 1
          Set sh = Sheets(.List(i))
          Erase a
          a = sh.Range("A2:J" & sh.Range("A" & Rows.Count).End(3).Row).Value
          For j = 1 To UBound(a, 1)
            m = m + 1
            For k = 1 To UBound(a, 2)
              b(m, k) = a(j, k)
            Next k
          Next j
        Next i
        sht.Range("A1").Value = "DATE"
      End If
    Else
      If OptionButton1 Then
        Set sh = Sheets(.Value)
        ReDim b(1 To sh.Range("A" & Rows.Count).End(3).Row - 1, 1 To 11)
        Call fillarray(dic, sh, b)
      Else
        b = Sheets(.Value).Range("A2:J" & Sheets(.Value).Range("A" & Rows.Count).End(3).Row).Value
        sht.Range("A1").Value = "DATE"
      End If
    End If
  End With
  
  sht.Range("A2:J" & Rows.Count).ClearContents
  sht.Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  ListBox1.RowSource = sht.Name & "!" & "A2:J" & sht.Range("A" & Rows.Count).End(3).Row
End Sub

Sub fillarray(dic, sh, b)
  Dim a() As Variant
  Dim j As Long, k As Long, m As Long, n As Long

  Erase a
  a = sh.Range("A2:J" & sh.Range("A" & Rows.Count).End(3).Row).Value
  For j = 1 To UBound(a, 1)
    If Not dic.exists(a(j, 2)) Then
      m = m + 1
      n = 1
    Else
      m = Split(dic(a(j, 2)), "|")(0)
      n = Split(dic(a(j, 2)), "|")(1) + 1
    End If
    dic(a(j, 2)) = m & "|" & n
    For k = 1 To UBound(a, 2)
      Select Case k
        Case 1
          b(m, k) = m
        Case 2 To 7
          b(m, k) = a(j, k)
        Case 8
          b(m, k) = b(m, k) + a(j, k)
        Case 9
          b(m, 11) = b(m, 11) + a(j, k)
          b(m, k) = b(m, 11) / n
        Case 10
          b(m, k) = b(m, 8) * b(m, 9)
      End Select
    Next
  Next
End Sub

Private Sub UserForm_Initialize()
  Dim i As Long
  
  With ComboBox1
    .AddItem "SH1"
    .AddItem "SH2"
    .AddItem "SH3"
  
    'After loading the names of the sheets in the combobox, put this code.
    For i = 0 To .ListCount - 1
      lr = lr + Sheets(.List(i)).Range("A" & Rows.Count).End(3).Row - 1
    Next
    Set sht = Sheets("Temp")
    sht.Range("A1:J1").Value = Sheets(.List(1)).Range("A1:J1").Value
    
    ListBox1.ColumnHeads = True
    OptionButton1.Value = True
  End With
End Sub
 
Upvote 0
Solution
You didn't put that option in the original post. That is why the code gets the result of option 1 (empty combo).
Click to expand...
my apologies !

fantastic ! you achieved too impressive work !
million thanks for a great help ;)
 
Upvote 0
@DanteAmor the code is excellent . may you mod this code for me please ?

I ask about add textbox1 . it should filter data based on column(2) "NAME" in listbox after show data in listbox when select the conditions as above .
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
288
shift-del
shift-del
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
343
JMWh1t3
J
A
  • Solved
Compare two sheets based on lastrow with highlighted cells
2 3
Replies
26
Views
341
mumps
mumps
O
  • Solved
Merge data in listbox based on another listbox after search for name and dates
Replies
6
Views
187
Omran Y
O
I
  • Question Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
973
Insurance Dave
I

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

Share this page

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