Add sparklines automatically to a pivot table column|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You can also do it with code. Add this to a normal module:

Sub UpdatePivotSparklines(pt As PivotTable)
    With pt
        Dim sparkRange As Range
        Set sparkRange = .DataBodyRange.Offset(, .DataBodyRange.Columns.Count).Resize(, 1)
    End With
    pt.TableRange1.Worksheet.UsedRange.SparklineGroups.Clear
    With sparkRange.SparklineGroups
        .Add Type:=xlSparkLine, SourceData:=pt.DataBodyRange.Address

With .Item(1)
With .SeriesColor
.Color = 9592887
.TintAndShade = 0
End With
With .Points
.Highpoint.Visible = True
.Lowpoint.Visible = True
.Firstpoint.Visible = True
.Lastpoint.Visible = True
.Markers.Visible = True
With .Negative.Color
.Color = 208
.TintAndShade = 0
End With
With .Markers.Color
.Color = 208
.TintAndShade = 0
End With
With .Highpoint.Color
.Color = 208
.TintAndShade = 0
End With
With .Lowpoint.Color
.Color = 208
.TintAndShade = 0
End With
With .Firstpoint.Color
.Color = 208
.TintAndShade = 0
End With
With .Lastpoint.Color
.Color = 208
.TintAndShade = 0
End With
End With
.Axes.Horizontal.RightToLeftPlotOrder = True
End With
End With
End Sub

then right click the pivot table worksheet, choose View Code and paste the following code into the code pane:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
UpdatePivotSparklines Target
End Sub

Macro enabled version of your file attached.

I do like the idea of a sparkling sheet! 😉

If you already have the sparklines in place, then the code can be a lot simpler:

Sub UpdatePivotSparklines(pt As PivotTable)
   With pt
      Dim sparkRange As Range
      Set sparkRange = .DataBodyRange.Offset(, .DataBodyRange.Columns.Count).Resize(, 1)
   End With
   With pt.TableRange1.Worksheet.UsedRange.SparklineGroups.Item(1)
      Set .Location = sparkRange
      .SourceData = pt.DataBodyRange.Address
   End With
End Sub

That routine replaces the previous one of the same name. The worksheet code that calls it remains the same.

as long as there are spark lines on the pivot sheets it should work.

I don’t really understand. One code adds and formats spark lines while the other only adjusts the location and data range of existing spark lines. It doesn’t make sense to combine them.

Unfortunately there is no option in VBA to simply apply a theme to a sparkline. You have to individually set the colours of the line, marker, high point, low point, first point and last point for example. Doing that randomly will most likely produce terrible combinations a lot of the time. I would personally suggest that you pick a theme and stick to it.

If you really wanted to pursue it, one option might be to create arrays with related colour values in (eg one has colour values for the line, one for the markers and so on) then use something like Randbetween to select a particular item from each array. At least that way you can design some combinations that work well beforehand.