CODEWITHSUNDEEP
excelvbapivotpivot-table
ppgcc74
ppgcc74

Reputation: 397

VBA: cannot work with PivotTable (error 1004)

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.

Upvotes: 0

Views: 570

Answers (2)

Vityata
Vityata

Reputation: 43595

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

Upvotes: 1

FAB
FAB

Reputation: 2569

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

Upvotes: 2

Related Questions