Option Explicit
Private Sub CommandButton1_Click()
Dim tng, dng
Dim rng As Range
Sheet3.[A7:BE65536].ClearContents
Sheet4.[A7:BE65536].ClearContents
With HS
On Error GoTo thoat
.Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter Field:=2, _
Criteria1:=">=" & IIf(Me.Cb_tu = "", Cb_tu.List(0), Cb_tu), Operator:=xlAnd, _
Criteria2:="<=" & IIf(Cb_den = "", Cb_den.List(Cb_den.ListCount - 1), Cb_den)
If Cb_M <> "" Then .Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter Field:=13, Criteria1:=Cb_M
If Cb_N <> "" Then .Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter Field:=14, Criteria1:=Cb_N
If Cb_O <> "" Then .Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter Field:=15, Criteria1:=Cb_O
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows. _
Count - 1).SpecialCells(xlCellTypeVisible)
rng.Copy Destination:=Sheet3.[B7]
.Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter
.Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter Field:=2, _
Criteria1:="=" & IIf(Me.ComboBox1 = "", Me.ComboBox1.List(0), Me.ComboBox1)
If Cb_M1 <> "" Then .Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter Field:=13, Criteria1:=Cb_M1
If Cb_N1 <> "" Then .Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter Field:=14, Criteria1:=Cb_N1
If Cb_O1 <> "" Then .Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter Field:=15, Criteria1:=Cb_O1
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows. _
Count - 1).SpecialCells(xlCellTypeVisible)
rng.Copy Destination:=Sheet4.[B7]
thoat:
.Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter
End With
If WorksheetFunction.CountA(Sheet3.[D7:D65536]) > 0 Then
With Sheet3.[A7].Resize(Sheet3.[c65536].End(xlUp).Row - 6)
.Formula = "=row()-6"
.Value = .Value
End With
End If
If WorksheetFunction.CountA(Sheet4.[D7:D65536]) > 0 Then
With Sheet4.[A7].Resize(Sheet4.[c65536].End(xlUp).Row - 6)
.Formula = "=row()-6"
.Value = .Value
End With
End If
Sheet1.Activate
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Sheet1.Select
HS.Range("A3:BE" & HS.[a65536].End(xlUp).Row).AutoFilter
Me.ComboBox1.List() = Nguon(Range(HS.[b5], HS.[b65536].End(xlUp)))
Me.Cb_tu.List() = Nguon(Range(HS.[b5], HS.[b65536].End(xlUp)))
Me.Cb_den.List() = Nguon(Range(HS.[b5], HS.[b65536].End(xlUp)))
Me.Cb_M.List() = Nguon(Range(HS.[m5], HS.[m65536].End(xlUp)))
Me.Cb_N.List() = Nguon(Range(HS.[n5], HS.[n65536].End(xlUp)))
Me.Cb_O.List() = Nguon(Range(HS.[o5], HS.[o65536].End(xlUp)))
Me.Cb_M1.List() = Nguon(Range(HS.[m5], HS.[m65536].End(xlUp)))
Me.Cb_N1.List() = Nguon(Range(HS.[n5], HS.[n65536].End(xlUp)))
Me.Cb_O1.List() = Nguon(Range(HS.[o5], HS.[o65536].End(xlUp)))
End Sub
Function Nguon(Rg As Range)
Dim Clls As Range, mg()
Dim loc As New Collection
Dim i As Integer
On Error Resume Next
For Each Clls In Rg
loc.Add Clls.Value, CStr(Clls.Value)
Next Clls
ReDim mg(loc.Count - 1)
For i = 1 To loc.Count
mg(i - 1) = loc(i)
Next i
Nguon = mg
End Function