Pivot Chart Groups Display Vertical Separator

[Grasor]

Hello All,

Been a stint since I was on the boards last but as usual we all find out way back here eventually. Today I was building a PivotTable with an accompanying PivotChart. The table is simple, it just groups Net Income by individual sources of income for each month by year. The PivotChart is a Stacked Bar Chart so all sources of income in a month are stacked on each other. The chart shows a column for each January first, then each February, etc. The problem is that it is difficult to clearly see where the "Januarys" end and the "Februarys" begin.

Attached is a snippet image of the chart.

How can I extend the lines on the horizontal axis to the top of the chart between each group of months?

Thanks as always.
G

 

[Worf]

Hello

In the example below, there are three data fields and five pivot items. This method works if all the month blocks have the same width.

Sub Grasor()
Dim pa As PlotArea, co As ChartObject, L As Shape, ax As Axis, _
pt As PivotTable, r As PivotField, un, i%
ActiveWindow.Zoom = 100
Set pt = Sheets("pivotsheet").PivotTables(1)
Set r = pt.RowFields(1)
Set co = ActiveSheet.ChartObjects("chart1")
With co.Chart
    Set ax = .Axes(xlCategory)
    Set pa = .PlotArea
End With
un = pa.InsideWidth / (r.PivotItems.Count * pt.DataFields.Count)
For i = 1 To r.PivotItems.Count - 1
    Set L = co.Chart.Shapes.AddLine(pa.InsideLeft + 3 * un * i, _
    pa.InsideTop, pa.InsideLeft + 3 * un * i, ax.Top)
    L.Line.ForeColor.RGB = RGB(100, 200, 10)
    L.Line.Weight = 2
Next
End Sub

 

[Grasor]

Worf,

Thanks! Will check this out and let you know if I need more assistance.

-G