Sub CreerTCD()
Dim X, Y As Variant
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Y = ws.Name
X = ws.Name & " " & "TCD"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = X
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
[y!A1].CurrentRegion.Address(, , xlR1C1, True)).CreatePivotTable
TableDestination:="x!R3C1", _
TableName:="TCD"
ActiveWorkbook.Sheets("x").PivotTables("Mon TCD").SmallGrid = False
ActiveWorkbook.Sheets("x").PivotTables("Mon TCD").AddFields RowFields:="ARTICLE", ColumnFields:="ANNEE MOIS" ', PageFields:=""
With ActiveWorkbook.Sheets("x").PivotTables("Mon TCD").PivotFields("km")
.Orientation = xlDataField
.Caption = "somme Qte_dem_km"
.Function = xlSum
'
End With
Application.CommandBars("PivotTable").Visible = True
Next ws
End Sub