Fetching Data from PivotTable into Array in VBA (Excel)

Question

i'm trying to fetch the Data from a PivotTable, use it in Array in VBA and then to print it. I'm new to VBA and I've watched a few tutorials, but i can't actually get it.

I've tried with referencing the range of my column with "DataBodyRange", but i always get different errors. "Sheet4" is the sheet where my "PivotTable1" is located. And i need all the data from a column.

Public Sub ReadToArray()
    'Range
    Dim rg As Range
    Set rg = Worksheets("Sheet4").pt("PivotTable1").DataBodyRange

    'Dynamic Array
     Dim Done As Variant
     Done = rg.Value

    'Array Values
     Debug.Print "i", "Value"
     Dim i As Long
     For i = LBound(Done) To UBound(Done)
         Debug.Print i, Done(i)
     Next i
 End Sub

The end result is that I want to print out the values for the whole column and use them afterwards.

Greg Viers · Accepted Answer · 2019-05-28 12:36:49Z

1

So I can see a few problems that are causing this. FIrst, to reference a pivot table in a sheet, you need .pivottables() not .pt().

Next, setting an array to have the value from a range like this will give you a 2D array, so you need to loop through it in two dimensions to get all the values. I've added a nested loop using a second iterator, j:

Public Sub ReadToArray()

     Dim pt As PivotTable
     Dim rg As Range
     Set pt = Worksheets("Sheet4").PivotTables("PivotTable1")

     Set rg = pt.DataBodyRange

     Dim Done As Variant

     Done = rg.Value

     Debug.Print "i", "Value"
     Dim i As Long, j As Long

     For i = LBound(Done, 1) To UBound(Done, 1)
        For j = LBound(Done, 2) To UBound(Done, 2)
            Debug.Print i & ", " & j & ", " & Done(i, j)
        Next j
     Next i

 End Sub

answered May 28, 2019 at 12:36

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

specbk Over a year ago

Thanks for the help, it works! Do you know how can i make the values for Done(i, j) to display only once and not twice? I mean that when i make 'Debug.Print i &' , " & j & ", " & Done(i, j)" 'to Debug.Print Done(i, j)' the values repeat themselves.

Greg Viers Over a year ago

Do they show with different j values? It could be because you have some sub or grand totals turned on in your pivot table.

Collectives™ on Stack Overflow

Fetching Data from PivotTable into Array in VBA (Excel)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related