Sub TCDAT()
'
' TCDAT Macro
'
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DSN!R1C1:R6000C38", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Récapitulatif!R1C10", TableName:= _
"Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion14
Sheets("Récapitulatif").Select
Cells(1, 10).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"CODE_DE_SIRET")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"TX_AT_TRANS_23_003")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique1").PivotFields("M_ASSIETTE_23_004"), _
"Nombre de M_ASSIETTE_23_004", xlCount
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Nombre de M_ASSIETTE_23_004")
.Caption = "Somme de M_ASSIETTE_23_004"
.Function = xlSum
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"CODE_DE_SIRET").Orientation = xlHidden
ActiveWorkbook.ShowPivotTableFieldList = False
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"TX_AT_TRANS_23_003")
.PivotItems("0").Visible = False
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect _
"TX_AT_TRANS_23_003[All]", xlLabelOnly, True
Columns("J:K").Select
Selection.Copy
Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("J:K").Select
Range("K1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("J:K").EntireColumn.AutoFit
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$L$2"), , xlYes).Name = _
"Tableau2"
Range("Tableau2[[#All],[Colonne1]]").Select
ActiveSheet.ListObjects("Tableau2").TableStyle = "TableStyleLight9"
Columns("J:K").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$J:$K"), , xlYes).Name = _
"Tableau3"
Columns("J:K").Select
ActiveSheet.ListObjects("Tableau3").TableStyle = "TableStyleMedium19"
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
Range("L1").Select
ActiveCell.FormulaR1C1 = "DADS"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Ecart"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=+[@[Somme de M_ASSIETTE_23_004]]-[@DADS]"
Range("Tableau3[[#Headers],[TX_AT_TRANS_23_003]]").Select
End Sub