VBA Problem with .CurrentPage in pivot table

[Palucci]

I would like to be able to select filtered items in this pivot table, such as CRD_RWG = 1. However, my code below shows me Application or object definied error. If I add .CurrentPage to ("CRD_RWG"). CurrentPage shows me that Run-time error 1004. Unable to set CurrentPage property of PivotField class

My part of code :

  With sh_d.PivotTables(tbName1)
   With .PivotFields("CRD_RWG")
    .ClearAllFilters
     .Orientation = xlPageField
     .Position = 1
        .CurrentPage = 1

similiar cross post : VBA Application definied error in pivot table

 

[Alex Blakenburg]

I started with your previous thread and there are a lot of errors in the code you posted there.
VBA problem with Applacation - definied in pivot table

I suggest you turn on Option Explicit, it would pick up a lot of the errors such as using lr when you have defined lr1 and tablename when you have defined tablename1.

In terms of the specific question in this thread you need this line

  .Orientation = xlPageField

This should be a full working version of your code.

Sub CreatePivot_v02()

    Dim tbName As String
    Dim lr1 As Long, ptver1 As Long
    Dim tbName1 As String
   
    Dim wbMe As Workbook
    Dim sh_s As Worksheet, sh_d As Worksheet
    Set wbMe = ActiveWorkbook
   
    Set sh_s = wbMe.Sheets("kredyty")
    lr1 = sh_s.Cells(Rows.Count, "A").End(xlUp).Row
    ptver1 = 6
    tbName1 = "Tabela przestawna"
    Sheets.Add after:=Sheets(Sheets.Count)
    Set sh_d = ActiveSheet
   
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=sh_s.Name & "!R1C1:R" & lr1 & "C12", Version:=ptver1).CreatePivotTable _
        TableDestination:=sh_d.Name & "!R3C1", TableName:=tbName1, _
        DefaultVersion:=ptver1

    With sh_d.PivotTables(tbName1).PivotFields("CRD_RWG")
        .Orientation = xlPageField
        .Position = 1
        .ClearAllFilters
        .CurrentPage = 1
    End With
   
    With sh_d.PivotTables(tbName1).PivotFields("NEW_DEFAULT")
        .Orientation = xlRowField
        .Position = 1
    End With
   
    With sh_d.PivotTables(tbName1)
        .AddDataField .PivotFields("CRD_EKSP_PIER_DC_FIN"), "Suma z Ekspozycji", xlSum
        .AddDataField .PivotFields("CRD_KOR_DC_FIN"), "Suma z Korekty", xlSum
    End With

End Sub

 

[Palucci]

Awesome ! Thanks ! a Option should be on start my macro ?

 

[Alex Blakenburg]

Unless you have turned it off, generally when you create a new module it will automatically put the words "Option Explicit" at the top if the module.

You can get more info here:How to use Option Explicit Statement in VBA

 

[Palucci]

@Alex Blakenburg i have one more qeustion if I would like to switch options in xlPageField several times and post the xlSum results next to it, how should I do it. My code shows me the error "Application-definied or object definied error"

  Set sh_s = wbMe.Sheets("kredyty")
    lr1 = sh_s.Cells(Rows.Count, "A").End(xlUp).Row
    ptver1 = 6
    tbName1 = "Tabela przestawna"
    Sheets.Add After:=Sheets(Sheets.Count)
    Set sh_d = ActiveSheet
   
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=sh_s.Name & "!R1C1:R" & lr1 & "C60", Version:=ptver1).CreatePivotTable _
        TableDestination:=sh_d.Name & "!R3C1", TableName:=tbName1, _
        DefaultVersion:=ptver1

    With sh_d.PivotTables(tbName1).PivotFields("CRD_RWG")
        .Orientation = xlPageField
        .Position = 1
        .ClearAllFilters
        .CurrentPage = 1
    End With
   
    With sh_d.PivotTables(tbName1).PivotFields("NEW_DEFAULT")
        .Orientation = xlPageField
        .Position = 1
        .ClearAllFilters
        .CurrentPage = 1
    End With
   
    With sh_d.PivotTables(tbName1)
        .AddDataField .PivotFields("CRD_EKSP_PIER_DC_FIN"), "Suma z Ekspozycji", xlSum
        .AddDataField .PivotFields("CRD_KOR_DC_FIN"), "Suma z Korekty", xlSum
    End With
wbMe.Sheets("Arkusz4").Range("A5:B5").Copy
wbMe.Sheets("Arkusz4").Range("A7").PasteSpecial Paste:=xlPasteValues
    With sh_d.PivotTables(tbName1).PivotFields("CRD_RWG")
        .Orientation = xlPageField
        .Position = 1
        .ClearAllFilters
        .CurrentPage = 1.5
    End With
    With sh_d.PivotTables(tbName1).PivotFields("NEW_DEFAULT")
        .Orientation = xlPageField
        .Position = 1
        .ClearAllFilters
        .CurrentPage = 0
    End With
    With sh_d.PivotTables(tbName1)
        .AddDataField .PivotFields("CRD_EKSP_PIER_DC_FIN"), "Suma z Ekspozycji", xlSum
        .AddDataField .PivotFields("CRD_KOR_DC_FIN"), "Suma z Korekty", xlSum
    End With

wbMe.Sheets("Arkusz4").Range("A5:B5").Copy
wbMe.Sheets("Arkusz4").Range("A8").PasteSpecial Paste:=xlPasteValues

 

[Alex Blakenburg]

You are not giving me much to go on.
Show me your error dialogue box and what line is highlighted in the code when it fails and you click on debug.

 

[Alex Blakenburg]

The most likely cause of your error message is that the value you have in the line(s) CurrentPage = 0 or some value , does not exist in your data set.

Ideally you need to work out what you want the code to do if the filter value does not exist and write some code to handle it.
As a quick and dirty solution you can add On Error around the CurrentPage lines eg

        On Error Resume Next
        .CurrentPage = 0
        On Error GoTo 0