Dynamically updating Sparklines in VBA Code

[Minerva7]

Hello,

I have an Excel file, with multiple tabs, in which I pull in data (daily) using VBA. I use sparklines to show trends, and I usually do this part of it manually (basically extending the sparkline to the next cell, including cells that have a value of zero). I'm looking for a way to make this as efficient as possible by writing in more VBA code that will automatically extend the sparkline to the next cell containing data. Can anyone help? Thanks in advance.

 

[Rijnsent]

Hi Minerva,
try the macro recorder, when I manually change a sparkline I get:

ActiveCell.SparklineGroups.Item(1).Modify Location:=Range("$L$3"), SourceData:="E3:K3"

And from the microsoft site:
SparklineGroup.Modify method (Excel) ->

Range("A1:A4").Select 
ActiveCell.SparklineGroups.Item(1).Modify Location:=Range("$A$1:$A$3"), SourceData:="Sheet1!B1:D3"

You can use parts of that code to add to your code, first inserting the data and after that setting the source of the various spark charts.
Hope that helps,
Koen

 

[Minerva7]

Thanks so much for the response! I'll try that.