Hello !
I try to extract datas from a pivottable.
This table is composed by 2 fields: "type plate" and "batch"
The field "batch" is chosen by the user by inputbox
The items of the second field "type of plate" by default set visible
The data field is the sum of the "type of plate"
I want to extract a table giving the sum of the "type of plate" by batch to make calculation on it.
Problem: it exists some items from the field "type plate" not displayed in the pivot table.
In my Macro, I calculate the value corresponding at each pivotItem of the pivotfield "type of plate".
But when the macro calculates "valeur" with a pivotitems not displayed in the pivottable, i have an error "runtime error 1004 application-defined".
Dim pt1 As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pj As PivotItem
Dim i As Double
Dim valeur As String
Set pt1 = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt1.PivotFields("Type plate")
p = 0
i = 10
For Each pi In pf.VisibleItems
Cells(3, 1).Select
On Error Goto 1
valeur = ActiveCell.PivotTable.GetPivotData("Number of plates in", "Type plate", pi.Value)
Cells(i, 2) = valeur
Cells(i, 1) = pi.Value
i = i + 1
p = p + 1
solution:
Next pi
I tried to go through this problem using "on error goto solution" but it works only one time.
Question: How can I the datas only appearing in this particular Pivottable ?
See attached a Picture of my table
Thank you to help me
Eloa
Bookmarks