Unable to use current page prop of pivotfeild class

karthuappu

New Member
Joined
May 31, 2019
Messages
10
i tried recording this macro but its giving me an error on the highlighted line,
can anyone help

Code:
Sub fxgxfbcfb()
'
' fxgxfbcfb Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Data!R1C1:R563451C27", Version:=6).CreatePivotTable TableDestination:= _
        "PivotTable!R5C3", TableName:="PivotTable4", DefaultVersion:=6
    Sheets("PivotTable").Select
    Cells(5, 3).Select
    With ActiveSheet.PivotTables("PivotTable4")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable4").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Buffer Size")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Stor Class")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Inv At Site"), "Sum of Inv At Site", xlSum
    Windows("Pivot_Store availability surplus & shortage.xlsb").Activate
    Windows("Custom Report - MTSSKUS Footwear Delhi_DelhiNCR_27th May.xlsb"). _
        Activate
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Buffer Size"), "Sum of Buffer Size", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("SL-SKU"), "Count of SL-SKU", xlCount
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Buffer Size"). _
        ClearAllFilters
[COLOR=#00ff00][B]        ActiveSheet.PivotTables("PivotTable4").PivotFields("Buffer Size").CurrentPage _[/B][/COLOR]
[COLOR=#00ff00][B]        = "0"[/B][/COLOR]
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Stor Class"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Stor Class").CurrentPage = _
        "FRESH 01"
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Stor Class").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Stor Class")
        .PivotItems("FO").Visible = False
        .PivotItems("Fresh").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Stor Class"). _
        EnableMultiplePageItems = True
End Sub
 
Last edited by a moderator:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top