.GetPivotData makes me crazy, please help

[George___]

Hallo,

I am quite newbie in writing VBA codes. I use VBA occasionally in my work but it saves me many times. Now I am facing issue with .GetPivotData which I am trying to use for thefirst time but still get Runtime error 1004. My code is as follows:
Sub data()

Dim IKN_PT As PivotTable
Set IKN_PT = Sheet12.PivotTables(1)



For i = 1 ToIKN_PT.PivotFields("Odběratel").PivotItems.Count

Cells(i,10).Value = IKN_PT.PivotFields("Odběratel").PivotItems(i).Name
Next i


For j = 1 ToIKN_PT.PivotFields("Odběratel").PivotItems.Count

Dim OdberatelAs Range
Set Odberatel =Cells(j, 10)

Dim IKN_suma AsRange
Set IKN_suma =IKN_PT.GetPivotData(DataField:="Pohledávka CZK",Field1:="Odběratel", Item1:=Odberatel)

Cells(j, 11) =IKN_suma.Value

Next j
End Sub

The raw with bold end with error 1004. When I replace “Odberatel”with “Range(“J1”) code works but logically with absolute reference to "J1" and Iwant to place relative reference to J column. I have tried many ways, go through forums,etc. For example I have tried:


Set IKN_suma = IKN_PT.GetPivotData("PohledávkaCZK", "Odběratel", Cells(j, 10).Value)


Set IKN_suma = IKN_PT.GetPivotData("PohledávkaCZK", "Odběratel", Cells(j, 10))


Set IKN_suma = IKN_PT.GetPivotData("PohledávkaCZK", "Odběratel", Range(“J” + j))

Set IKN_suma = IKN_PT.GetPivotData("PohledávkaCZK", "Odběratel", Range(“J” & j))


but nothing works. I am really struggling…. Any ideas?





Thanks a lot in advance.



George

 

[George___]

Hi guys,

I have moved forward a bit. My code basically works but Imade stupid mistake. I have filter in my pivot table and I expect that line:

“Cells(i, 10).Value =IKN_PT.PivotFields("Odběratel").PivotItems(i).Name “

takes into effect only filtered Pivotitems and not all of them. And to my luckimmediatelly first PivotItem has no value when my filter in pivottable has beenapplied, therefore error 1004 occurs. So I need to find out way how to list just filtered PivotItems and itwill be working finally. Thanks for advice.

George