Bonjour,
Ton fichier en retour avec avant tout la mise en forme des données en tableau (dynamique).
A te relire.
Cdlt.
Option Explicit
Public Sub CreatePivotTables()
Dim wb As Workbook
Dim wsData As Worksheet, wsPT As Worksheet
Dim rngPT As Range
Dim PTCache As PivotCache
Dim pt As PivotTable
Application.ScreenUpdating = False
'---------------------------------------------------------------------------------
Set wb = ActiveWorkbook
Set wsData = wb.Worksheets("Données")
Set rngPT = wsData.ListObjects(1).Range
Set wsPT = wb.Worksheets("Analyse")
'---------------------------------------------------------------------------------
On Error Resume Next
For Each pt In wsPT.PivotTables
pt.TableRange2.Clear
Next pt
On Error GoTo 0
'---------------------------------------------------------------------------------
Set PTCache = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngPT)
'---------------------------------------------------------------------------------
Set pt = PTCache.CreatePivotTable(wsPT.Cells(3, 1), TableName:="PT_1")
With pt
.ManualUpdate = True
.AddFields RowFields:="Nom"
With .PivotFields("Poste")
.Orientation = xlDataField
.Function = xlCount
.NumberFormat = "#,##0"
.Caption = "NB poste"
End With
.RowAxisLayout xlTabularRow
.ManualUpdate = False
End With
'---------------------------------------------------------------------------------
Set pt = PTCache.CreatePivotTable(wsPT.Cells(3, 4), TableName:="PT_2")
With pt
.ManualUpdate = True
.AddFields RowFields:="Ville"
With .PivotFields("Poste")
.Orientation = xlDataField
.Function = xlCount
.NumberFormat = "#,##0"
.Caption = "NB poste"
End With
.RowAxisLayout xlTabularRow
.ManualUpdate = False
End With
'---------------------------------------------------------------------------------
Set pt = Nothing
Set PTCache = Nothing
Set rngPT = Nothing
Set wsPT = Nothing: Set wsData = Nothing
Set wb = Nothing
End Sub