Sub LayVT1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'tham khao NVSON
Dim DataSheet As Worksheet
Sheets("bkdm").Select
Range("KetQuaLoc2").Select
Selection.ClearContents
Set DataSheet = Sheets("Data")
DataSheet.Activate
'xoa extract cu
Columns("G:J").Select
Selection.ClearContents
'tao ra ketqualoc
Range("BKDinhMuc").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("bkdm").Range("VungLoc"), CopyToRange:=Range("KetQuaLoc"), Unique:=False
With ActiveWorkbook
.Names("_FilterDatabase").Delete
.Names("Extract").Delete
End With
'tao ra ketqualoc2
Range("VungLoc1").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"KetQuaLoc2"), Unique:=True
Range("KetQuaLoc").Select
Selection.ClearContents
Sheets("bkdm").Select
Sheets("bkdm").Names("Extract").Delete
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub Sub LayVT2()
'gan vao bang ke
Dim so_record As Integer
Dim so_mahang As Integer
Sheets("bkdm").Select
Range("D1:AQ11").Select
Selection.ClearContents
so_record = WorksheetFunction.CountA(Range("ketqualoc2")) - 1
so_mahang = WorksheetFunction.CountA(Range("vungloc")) - 1
For i = 1 To so_record
Cells(1, 2 * i + 2).Value = Cells(24 + i, 4).Value
Cells(2, 2 * i + 2).Value = "Dinh Muc"
Cells(2, 2 * i + 3).Value = "SLVatTu"
For k = 0 To so_mahang - 1
Cells(3 + k, 2 * i + 2).Value = "=SUMPRODUCT((MAHANG=RC1)*(TENVT=R1C)*DINHMUC)"
Cells(3 + k, 2 * i + 3).Value = "=RC3*RC[-1]"
Next k
'dong tong
Cells(3 + so_mahang, 2 * i + 2).Value = "=SUM(R3C:R[-1]C)"
Cells(3 + so_mahang, 2 * i + 3).Value = "=SUM(R3C:R[-1]C)"
Next i
Range("d0" & so_mahang + 3 & ":AQ0" & so_mahang + 3).Select
Selection.Font.Bold = True
End Sub
