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
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: