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