VBA: cannot work with PivotTable (error 1004)

Question

Okay, I really am frustrated right now. For the past hour or so I have been trying to get data from a simple Pivot. I cannot get it to work.

My really simple code:

Sub getXXFromPivot()

     Dim test As PivotTable

     Set Sheet1 = ActiveWorkbook.Sheets("Sheet5")
     Set test = Sheet1.PivotTables("PivotTable1")
     Set test2 = test.ClearAllFilters

     MsgBox test2

End Sub

I am trying to use the "GetPivotData" method to get data, but no matter what I use after test. (ClearAllFilters was just a test) I always end up with error 1004. What is my mistake here? I cannot figure it out. "PivotTable1" definitely exists. I can MsgBox test and get "PivotTable1" back.

You probably want to do test.ClearAllFilters instead of Set test2 = test.ClearAllFilters. Also, the message in the message box has to be a string, which test is not. Also you should declare Sheet1 with a different name. — FAB
– FAB, Commented May 31, 2019 at 10:48
Are you sure you get 1004? This code test2 = test.ClearAllFilters should return "type mismatch" error. — Vityata
– Vityata, Commented May 31, 2019 at 10:50
@DarXyde True, that was stupid. Clearing the filters works fine without the Set, but I still do not know how to get any data from the Pivot. Any thoughts? — ppgcc74
– ppgcc74, Commented May 31, 2019 at 11:01

FAB · Accepted Answer · 2019-05-31 10:52:40Z

2

Adding to my comment, please see bellow an adaptation of your code:

Option Explicit 'Is recommended to use this always
Sub getXXFromPivot()

    Dim sht As Worksheet
    Dim pTbl As PivotTable

    Set sht = ActiveWorkbook.Sheets("Sheet5")
    Set pTbl = sht.PivotTables("PivotTable1")
    pTbl.ClearAllFilters

    MsgBox pTbl.Name

End Sub

As a pretty good source on how to work with pivot tables, see The VBA Guide To Excel Pivot Tables

answered May 31, 2019 at 10:52

FAB

2,5791 gold badge12 silver badges22 bronze badges

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

2 Comments

ppgcc74 Over a year ago

Thank you. This works so far. What I need in the end is a List/an Array of the data in the Pivot. Its just one Column without section. How would I go about that?

Damian Over a year ago

use the function GetPivotData(field,pivottable,filter1,criteria1,filter2,criteria2...)

Vityata · Accepted Answer · 2019-05-31 11:01:36Z

You may decide to use TableRange1 or TableRange2properties of the PivotTable and loop through them. This is the main difference:

TableRange1 - Returns a Range object that represents the range containing the entire PivotTable report, but doesn't include page fields. Read-only.
TableRange2 - Returns a Range object that represents the range containing the entire PivotTable report, including page fields. Read-only.

Sub GetXXFromPivot()

     Dim pt As PivotTable
     Dim wks As Worksheet

     Set wks = Worksheets(1)
     Set pt = wks.PivotTables("PivotTable1")

     Dim myResult As Range
     Set myResult = pt.TableRange2

     Dim myCell As Range
     For Each myCell In pt.TableRange2.Cells
        Debug.Print myCell
     Next myCell

End Sub

Collectives™ on Stack Overflow

VBA: cannot work with PivotTable (error 1004)

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related