Re jean-Eric,
J'ai également testé ta proposition, cependant quand je lance la macro cela me fait la même erreur => Erreur d'exécution '13' : Incompatibilité de type.
La Partie surlignée en jaune est celle la :
Sub Solution_forum()
Dim Last
Last = [A160000].End(xlUp).Row 'dernière colonne en A
Set plg = Range("A3:AN" & Last) ' la zone devient variable en longueur
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
plg, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Feuil2!R3C8", TableName:="Tableau croisé dynamique1", _
DefaultVersion:=xlPivotTableVersion12
End Sub
Public Sub CreatePivotTable()
Dim wb As Workbook
Dim wsData As Worksheet, wsPT As Worksheet
Dim lCols As Long, lRow As Long, lRows As Long
Dim Rng As Range
Dim PTCache As PivotCache
Dim PT As PivotTable
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
'----------------------------------------------------------------------
Set wb = ActiveWorkbook
Set wsData = wb.Worksheets("Feuil1")
'----------------------------------------------------------------------
On Error Resume Next
wb.Worksheets("TCD").Delete
On Error GoTo 0
'----------------------------------------------------------------------
lRow = 3
'----------------------------------------------------------------------
With wsData
lRows = .Cells(.Rows.Count, 1).End(xlUp).Row
lCols = .Cells(3, .Columns.Count).End(xlToLeft).Column
Set Rng = .Cells(3, 1).Resize(lRows - lRow + 1, lCols)
End With
'----------------------------------------------------------------------
Set PTCache = wb.PivotCaches.Add(xlDatabase, Rng)
'----------------------------------------------------------------------
Set wsPT = wb.Worksheets.Add(after:=Worksheets(Worksheets.Count))
wsPT.Name = "TCD"
'----------------------------------------------------------------------
Set PT = PTCache.CreatePivotTable(wsPT.Cells(3, 1), "TCD_1")
'----------------------------------------------------------------------
Application.DisplayAlerts = True
'----------------------------------------------------------------------
Set PT = Nothing
Set PTCache = Nothing
Set Rng = Nothing
Set wsData = Nothing
Set wb = Nothing
End Sub