Thứ Sáu, 8 tháng 1, 2021

Tùy chỉnh Macros Pivot tổng hợp bảng tính tiền lương cho công ty gỗ

Sub PIVOT_LUONG()
On Error Resume Next
TAOSHEETPIVOT
Dim SH1 As Worksheet
Set SH1 = Sheets("PIVOT")
Sheets("Pivot").Range("C1").Value = "BAÙO CAÙO LÖÔNG"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"BKNX!A50:BC65536").CreatePivotTable TableDestination:= _
Sheets("Pivot").Range("A1"), TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1").PivotFields("SO CHUNG TU").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("NGAY CHUNG TU").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("DIEN GIAI").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("P. LOAI").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("CHAT LUONG").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("DAY").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("RONG").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("DAI").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("SO TAM").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("SO KHOI").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("DON GIA LUONG").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("THANH TIEN LUONG").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
'"SO TAM", "SO KHOI", "DON GIA LUONG", "THANH TIEN LUONG"
If F_BKNX.CHK_cot.Value = True Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("MA SP TINH LUONG").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("SO CHUNG TU", _
"NGAY CHUNG TU", "DIEN GIAI", "MA SP TINH LUONG", "P. LOAI", "CHAT LUONG", "DAY", "RONG", "DAI", "SO TAM", "SO KHOI", "DON GIA LUONG", "THANH TIEN LUONG"), ColumnFields:="TEN SP TINH LUONG", PageFields:= _
Array("THANG", "MA NV", "NHAP (N) , XUAT (X)", "MA KHO NHAP", "MA KHO XUAT")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SO KHOI")
.Orientation = xlDataField
.Caption = "Sum of SO KHOI"
.Function = xlSum
.NumberFormat = "#,##0.0000"
End With
Else
If F_BKNX.CHK_dong.Value = True Then
'TEN SP TINH LUONG
ActiveSheet.PivotTables("PivotTable1").PivotFields("MA SP TINH LUONG").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("TEN SP TINH LUONG", "SO CHUNG TU", _
"NGAY CHUNG TU", "DIEN GIAI", "P. LOAI", "CHAT LUONG", "DAY", "RONG", "DAI", "SO TAM", "SO KHOI", "DON GIA LUONG", "THANH TIEN LUONG"), PageFields:= _
Array("THANG", "MA NV", "NHAP (N) , XUAT (X)", "MA KHO NHAP", "MA KHO XUAT")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SO KHOI")
.Orientation = xlDataField
.Caption = "Sum of SO KHOI"
.Function = xlSum
.NumberFormat = "#,##0.0000"
End With
End If
End If
ActiveWorkbook.ShowPivotTableFieldList = False
'DINH DANG FONTS
Cells.Select
With Selection.Font
.Name = "VNI-Times"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub
Previous Post
Next Post

0 nhận xét: