Pivot Table

[xtinct]

how to use VBA to create a pivot table for the 2 columns?

and then from the pivot table to generate a line chart similar to

but with an additional constant line at Y :0.5 as an average?

 

[MarkAndrews]

Something like

    Columns("A:B").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!C1:C2").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Int fn 1"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("overlay fn").Orientation = _
        xlDataField
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("overlay fn")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Int fn 1'[All;Total]", _
        xlDataAndLabel
    Selection.Delete
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Int fn 1")
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("overlay fn")
        .PivotItems("(blank)").Visible = False
    End With
    Application.CommandBars("PivotTable").Visible = False
    Range("B5").Select
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("B5")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.ChartType = xlLineMarkersStacked
End Sub