Re : TCD plusieurs feuilles nom variable
j'ai trouvé
Application.ScreenUpdating = False
Dim wsn As String
Dim i As Integer
Dim prange As String
Dim PCrange As String
Dim rangearr() As String
ReDim Preserve rangearr(i To Worksheets.Count - 1)
For i = 1 To Worksheets.Count - 1 Step 1
wsn = Worksheets(i).Name
Sheets(wsn).Select
Range("A1
1").Select
Range(Selection, Selection.End(xlDown)).Select
prange = Selection.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=True, _
ColumnAbsolute:=True)
PCrange = wsn & "!" & prange
If (i - 3) < 0 Then rangearr(0) = PCrange _
Else: rangearr(LBound(rangearr) + (i - 2)) = PCrange
Next i
Sheets("synthese").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
rangearr).CreatePivotTable TableDestination:=Range("a1"), _
TableName:="PivotTable1"
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.HasAutoFormat = False
.RowGrand = False
.SmallGrid = False
End With
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
'ActiveSheet.PivotTables("PivotTable1").PivotFields("Data").PivotItems(
'"Sum of Value").Position = 1
Range("D1").Select
Application.CommandBars("PivotTable").Visible = False
End Sub