Macro error when using Dim variable

[blaksnm]

Hi guys
I try to update my pivot-tables (several) by:

Dim PRSK
PRSK = Range("ProsjektNrRegnskap").Value

PivotTables("PeTFakturert").PivotFields("HovedProsjektNr").ClearAllFilters
PivotTables("PeTFakturert").PivotFields("HovedProsjektNr").CurrentPage = PRSK ' error here
End If

But error occurs: "Run-time error 1004" Application-defined ot Object-defined error"
I used recording to programme and modyfied it afterwards.

Will someone guide me on this one?

 

[Rijnsent]

Hi Snoopy,
first of all, this is the page I generally get to when I'm stuck with VBA & Pivots: https://www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables
Next, the error occurs when you try to select an item in that's not in that field. So if the field has the items 1, 2 and 3 and you want to select 4 you'll get that error. There are two solutions:

'Option 1:
On Error Resume Next
PivotTables("PeTFakturert").PivotFields("HovedProsjektNr").CurrentPage = PRSK ' error here
On Error GoTo 0

'Option 2:
Set Sht = Worksheets("Sheet5")
PRSK = Sht.Range("K1").Value
Set piv = Sht.PivotTables("PivotTable1")

piv.PivotFields("a").ClearAllFilters
For Each pf In piv.PivotFields("a").PivotItems
    'This loops through all the values of field, compare here with PRSK
    Debug.Print pf.Value
Next pf

Hope that helps,
Koen

 

[blaksnm]

Nice!
Thanks a lot