4th Floor, 4 Tabernacle Street London EC2A 4LU

Data from a Pivot

see the example below:

[vb]

Sub PTTest()
Dim SH As Worksheet ‘ the current worksheet from the colection of workbooks
Dim PT As PivotTable ‘ the current pivot table from the current worksheet
Dim PTC As Range ‘ the cell range of the current pivot table
Dim Tmp AsString’ the buffer for concatenated cell values

Tmp = “”
‘ process all sheets, as Pivot table objects are contained by sheets
ForEach SH In ActiveWorkbook.Worksheets

ForEach PT In SH.PivotTables

ForEach PTC In PT.TableRange1.Cells
‘ all cells in one buffer, seperated by “;”
‘ if you want to include page header cells, use
‘ “PT.TableRange2.Cells” instead
Tmp = Tmp & PTC & “;”
Next PTC

‘ *** do something *** with the buffer
‘ ok very simple we print it into the debugger’s Immediate window
Debug.Print Tmp

‘ empty buffer for next pivot table
Tmp = “”

Next PT
Next SH
EndSub

 

[/vb]