Snasui Excel Forum

Code: Select all

Sheets.Add After:=ActiveSheet
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "By System"
'pivot Macro

    Range("A1").Select
    Dim Data As Range
    With ActiveSheet
        Set Data = .UsedRange
    End With
    Sheets.Add
	ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Data, Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Customer and Information Detail" & Chr(10) & "Incident No"), _
        "Count of Customer and Information Detail" & Chr(10) & "Incident No", xlCount
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("No.")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields( _
        "Work around" & Chr(10) & "Sub Category")
        .Orientation = xlRowField
        .Position = 1
    End With
    Range("A4").Select
    With ActiveSheet.PivotTables("PivotTable5")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    ActiveSheet.PivotTables("PivotTable5").PivotSelect _
        "'Work around" & Chr(10) & "Sub Category'[All]", xlLabelOnly, True
    Range("N5").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Work around" & Chr(10) & "Sub Category"). _
        AutoSort xlDescending, "Count of Customer and Information Detail" & Chr(10) & "Incident No", _
        ActiveSheet.PivotTables("PivotTable5").PivotColumnAxis.PivotLines(13), 1
    Range("A3:N24").Select
    Range("A13").Activate