Hi,

As you've found, it's not possible to include a Sparkline in a PivotTable. Please see this tutorial on relative dynamic named ranges. Towards the end there is an example that uses Sparklines.

I hope that points you in the right direction.

Mynda

Thanks a lot Mynda ... I will follow your advice.

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.

Hi,

Thanks a lot for your valuable time helping me.

Excellent indeed.

But, if I've several sheets (about 59 sheets), each one includes a pivot table, and each has sparklines beside it, of course.

Is it possible to set different colors and styles for each sparkling sheet? I do not want to let them all have the same look and theme, as with each pivot table refresh all sparklines reset to the same theme.

Thanks

MGB!

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

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

Is this code to be added after the last line of the original code above?

Or it is to replace the old code with the new one?

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

Please note that I can not determine the future growth of the pivot table growth, as many rows are to be added later on.

I've tried to paste the above code after the end of the original code but got an error.

Thanks a lot and much appreciated your valuable time helping me my friend.

MGB

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.

Thanks a lot for your valuable time replying to my thread and helping me.

But if I want to merge both VBAs into one single code, what would it be?

The reason for asking is that I need to add the sparklines to other workbooks and I want to randomize its theme.

So I want to keep the full code to achieve the 2 things in the same newly created workbook.

Thanks a lot.

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.

Thanks a lot.

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.