Sub Open_Projects_pivots()
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Open_Project_Details", Version:=6).CreatePivotTable TableDestination:= _
"Open Projects pivots!R1C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Open Projects pivots").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.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("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Labels")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("ShortId"), "Count of ShortId", xlCount
Range("A1:C4").Select
Selection.Copy
Range("A15").Select
ActiveSheet.Paste
ActiveSheet.PivotTables("PivotTable2").PivotFields("Labels").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Requester Team (string)")
.Orientation = xlRowField
.Position = 1
End With
End Sub
I am getting run time error 5 on this code and unsure what the error is. This is a recorded macros. FYI "Open_Project_Details" is a dynamic table in the sheet "Open Project Details" "Open Projects pivots" is the destination sheet and will always remain and this sheet will have a button click for this macros
Bookmarks