Pivot Table

xtinct

New Member
Joined
Sep 11, 2006
Messages
48
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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Something like

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

Watch MrExcel Video

Forum statistics

Threads
1,114,377
Messages
5,547,587
Members
410,801
Latest member
DataMgmtAnalyst7
Top