Bonjour,
Une proposition de ce que j'ai compris pour la construction des TCDs et des chronologies.
Cdlt.
Option Explicit
Public Sub CreatePTs()
Dim wb As Workbook
Dim ws As Worksheet, wsPT As Worksheet
Dim T() As String
Dim rngData As Range
Dim PTCache As PivotCache, PT As PivotTable
Dim SLCache As SlicerCache, SL As Slicer
Dim N As Long, i As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
If Left(ws.Name, 3) = "TCD" Then ws.Delete
Next ws
Application.DisplayAlerts = False
N = wb.Worksheets.Count
ReDim T(1 To N)
For i = 1 To N
T(i) = wb.Worksheets(i).Name
Next i
For i = 1 To UBound(T)
Set rngData = wb.Worksheets(T(i)).Cells(1).CurrentRegion
Set PTCache = wb.PivotCaches.Create(xlDatabase, rngData)
Set wsPT = wb.Worksheets.Add(after:=wb.Worksheets(Worksheets.Count))
wsPT.Name = "TCD_" & T(i)
Set PT = PTCache.CreatePivotTable(wsPT.Cells(4, 1), wsPT.Name)
With PT
.AddFields RowFields:="Domaine", PageFields:="Degr?"
With .PivotFields("Cours")
.Orientation = xlDataField
.Function = xlCount
.Caption = "Nb Cours"
End With
.RowAxisLayout xlTabularRow
.TableStyle2 = "PivotStyleLight16"
End With
Set SLCache = wb.SlicerCaches.Add2(PT, "Date", , xlTimeline)
Set SL = SLCache.Slicers.Add(wsPT, , "Date" & i, "Date", Cells(2, 4).Top, Cells(2, 4).Left)
wsPT.Cells(1).Select
ActiveWindow.DisplayGridlines = False
Next i
End Sub