Sub TinhPhuongAn()
With Application
.EnableEvents = False: .ScreenUpdating = False
.Calculation = xlCalculationManual
On Error GoTo ExitSub
Dim MaChuHang As String, MaCH As String, PhuongAn As Range, MyRng As Range
Dim SoHangMS As Byte, SoHangPA As Byte, i As Byte, a As Byte
Dim Ham: Set Ham = .WorksheetFunction
With Range(CSDL1.[A4], CSDL1.[A65536].End(xlUp)).Resize(, 20)
If .Rows.Count = 1 Then CSDL2.[A4:T5].Name = "NguonPivot" Else .Name = "NguonPivot"
End With
With BangPivot
.PivotTables("PivotTable1").PivotCache.Refresh
.[T:V].Clear
End With
Call PhucHoi ''<--- XONG SE XOA, hoac chuyen mã cho BangPhuongAn
If BangPhuongAn.[A8] <> "" Then
With BangPhuongAn
Range(.[A9], .[A48].End(xlUp)).Resize(, 3).Copy BangPivot.[T1]
.Range("A9:I48, L9:O48, Q9:Q48").ClearContents
End With
With BangPivot
SoHangMS = .[T65536].End(xlUp).Row
For i = 1 To SoHangMS
MaCH = .Range("T" & i)
MaChuHang = .[A1]
.PivotTables("PivotTable1").PivotFields(MaChuHang).CurrentPage = MaCH
Set PhuongAn = Range(.[B5], .[B65536].End(xlUp))
SoHangPA = Ham.CountA(PhuongAn)
Set MyRng = BangPhuongAn.[A48].End(xlUp)
With PhuongAn
.Copy: MyRng.Offset(1, 16).PasteSpecial 3
.Offset(, 1).Resize(, 6).Copy: MyRng.Offset(1, 3).PasteSpecial 3
.Offset(, 7).Resize(, 4).Copy: MyRng.Offset(1, 11).PasteSpecial 3
End With
.Range("T" & i).Resize(, 3).Copy: MyRng.Offset(1).PasteSpecial 3
If SoHangPA >= 2 Then
For a = 2 To SoHangPA
BangPhuongAn.[A48].End(xlUp).Offset(1) = BangPhuongAn.[A48].End(xlUp).Value
Next
End If
Next
.PivotTables("PivotTable1").PivotFields(MaChuHang).CurrentPage = "(All)"
.[T:V].Clear
End With
End If
With BangPhuongAn
.[A8:A48].AutoFilter 1, "<>", , , False
''.[A:A].EntireColumn.Hidden = True
End With
ExitSub:
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True: .EnableEvents = True
End With
End Sub
