Sub Macro1() ' ' Macro1 Macro ' ' ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Appraisal!R1C1:R248C13", Version:=6).CreatePivotTable TableDestination:= _ "Appraisal!R1C17", TableName:="PivotTable1", DefaultVersion:=6 Sheets("Appraisal").Select Cells(1, 17).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("Department") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Team") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("Status"), "Count of Status", xlCount End Sub