Sparklines are graphical representation of data in single cell. A way to visualize data in a cell by cell manner. As we know, we can plot multiple sparklines in excel cells. Thus, there will be large number of sparklines exist in our excel reports. Sometime, it is very difficult to modify them. However, these sparklines details can be collected using VBA code. Thus, we can modify them using such a details. In this article, we will learn how to capture sparklines details using excel macro.


  
Here is the VBA code to capture Sparkline details in a workbook: 

Option Explicit
Public Const spl_sht As String = "Sparkline_Details"

Sub List_Sparklines()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com/

'Declare variable
Dim spa As SparklineGroups
Dim sprln As SparklineGroup
Dim sht As Worksheet
Dim i As Integer
Dim flag As Boolean

'Disable screen updating
Application.ScreenUpdating = False
Application.DisplayAlerts = False

flag = False

'Check if Sparkline_Details sheet exist in active workbook
For Each sht In ActiveWorkbook.Worksheets
    If sht.Name = spl_sht Then
        flag = True
    End If
Next sht

'Add Sparkline_Details sheet if it not exists
If flag = False Then
    ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = spl_sht
End If

ActiveWorkbook.Sheets(spl_sht).Activate
ActiveWorkbook.Sheets(spl_sht).Range("A1:Z500").ClearContents
ActiveWorkbook.Sheets(spl_sht).Range("A1").Value = "Sheet Name"
ActiveWorkbook.Sheets(spl_sht).Range("B1").Value = "Sparkline Location"
ActiveWorkbook.Sheets(spl_sht).Range("C1").Value = "Sparkline SourceData"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

i = 1

'Check each worksheet for Sparkline details
For Each sht In ActiveWorkbook.Worksheets

    Set spa = sht.UsedRange.SparklineGroups

    For Each sprln In spa
        'Pass sheet name value to A column in Sparkline Details tab
        ActiveWorkbook.Sheets(spl_sht).Range("A" & i + 1).Value = sht.Name
        
        'Pass Location range of Sparkline group to B column in Sparkline Details tab
        ActiveWorkbook.Sheets(spl_sht).Range("B" & i + 1).Value = sprln.Location.Address
        
        'Pass Source Data range of Sparkline group to C column in Sparkline Details tab
        ActiveWorkbook.Sheets(spl_sht).Range("C" & i + 1).Value = sprln.SourceData
        i = i + 1
    Next sprln

Next sht

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "All Sparkline listed successfully." & vbNewLine & vbNewLine & "Refer " & spl_sht & " sheet for Sparkline Details", vbInformation

End Sub