Automate sparkline creation (loop help!)

[thebawp]

I have a spreadsheet which consists of two sheets - a summary sheet and a data sheet. On the summary sheet I have a unique list of names that exist in the data sheet in column A, and in the data sheet, there are multiple rows per name (column A) and a number next to each one which denotes 'visits' (column B). Each name may have a different number of rows.

'Summary Sheet'

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name 1[/TD]
[TD]Sparkline (Name1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name 2[/TD]
[TD]Sparkline (Name2)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name 3[/TD]
[TD]Sparkline (Name3)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name 4[/TD]
[TD]Sparkline (Name4)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name 5[/TD]
[TD]Sparkline (Name5)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name 6[/TD]
[TD]Sparkline (Name6)[/TD]
[/TR]
</tbody>[/TABLE]

'Data Sheet'

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name 1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name 1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name 2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name 3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name 3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Name 4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Name 4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Name 5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Name 5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Name 5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Name 6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Name 6[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


I have created a named range for each name in the summary sheet which follows this format:
Name1

=INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$1,'Data Sheet'!$A:$A,0)):INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$1,'Data Sheet'!$A:$A,1))

Name2

=INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$2,'Data Sheet'!$A:$A,0)):INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$2,'Data Sheet'!$A:$A,1))

Each range is named 'name1', 'name2', 'name3' etc, and returns the data in range for each name (i.e. for Name 1 the range returned would be B1:B3 in the Data Sheet). I then use each named range to generate a sparkline for each name in the summary sheet.

What I would like to be able to do is to automate this process, as the data will change on a regular basis, and there could be different names etc - whilst in the dummy data I have restricted it to just 6 names, the actual data could have hundreds of names, so to do this manually just isn't practical. I think VBA could be utilised to create each range but I'd like this to be done dynamically (i.e. based on the number of names in the summary sheet). I also think there are a few parts to the process i.e:

1. Create a named range for each name in the Summary Sheet that references to the Data Sheet (possibly using the above formula format although if there's a better way I'm happy to use it!).

I think this could be done using a loop to increment the cell reference (i.e. from A1 to A2 to A3 etc) based on each name in the list, I always struggle with loops so I'm looking for a little assistance in this area! The basis of creating the named range would be:

Sub AddNamedRange()


ActiveWorkbook.Names.Add Name:="Name1", RefersTo:= _
        "=INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$1,'Data Sheet'!$A:$A,0)):INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$1,'Data Sheet'!$A:$A,1))"
End Sub

2. Create a sparkline group in column B in the Summary Sheet for each unique name that uses the created named ranges specific to those names. The VBA for this seems quite simple if you know the range and you know the named ranges, but I'm not sure how I could feed the information from step 1 (i.e. all the created named ranges) into step 2, so again some assistance would be much appreciated!

Sub CreateSparkline ()    Range("$B$1:$B$5").SparklineGroups.Add Type:=xlSparkLine, SourceData:= _
        "Name1, Name2, Name3, Name4, Name5"
End Sub

I hope that all makes sense! Writing it out has given me some further thoughts so I will attempt to update the thread if I make any progress. Thanks

 

[thebawp]

In regards to part 1, I have managed to create a macro with some VBA I found online (which I have promptly lost the address of!).

Sub CreateMultipleNamedRangesBasedOnList()

Dim srcrng As Range
Dim namedref As String
Dim rng As Range
Dim RangeName As String
Dim i As Integer
Dim ii As Integer


i = 1

Worksheets("Summary Sheet").Activate
Set srcrng = Worksheets("Summary Sheet").Range("A2", ActiveSheet.Range("A2").End(xlDown))

    For Each cell In srcrng
        RangeName = "Name" & i
        namedref = "=INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$" & i & ",'Data Sheet'!$A:$A,0)):INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$" & i & ",'Data Sheet'!$A:$A,1))"
        
        Worksheets("Summary Sheet").Names.Add Name:=RangeName, RefersTo:=namedref
        
        i = i + 1
        
    Next cell
    
    MsgBox "Ranges Created."
    
End Sub

This creates all of the named ranges needed for part 2.

 

[thebawp]

Well with a little persistence I think I've solved this, it may be helpful for someone else in the future so here is the VBA used.

Sub Generate_Sparklines()

'This macro identifies and creates named ranges for each name present in the summary sheet based on
'the data in the data sheet and then uses this information to create a sparkline for each name in the summary list
Dim srcrng As Range
Dim DataRange As Range
Dim namedref As String
Dim RangeName As String
Dim myString As String
Dim i As Integer
Dim ii As Integer


'i is a counter used to generate named range names i.e. name1, name2, name3 etc
i = 1
'ii is a reference to the starting cell for the namedref formula i.e. the first formula makes reference to A2
ii = 2


Worksheets("Summary Sheet").Activate


'identifies the range of names
Set srcrng = Worksheets("Summary Sheet").Range("A2", ActiveSheet.Range("A2").End(xlDown))


    For Each cell In srcrng
    
        RangeName = "Name" & i


        namedref = "=INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$" & ii & ",'Data Sheet'!$A:$A,0)):INDEX('Data Sheet'!$B:$B,MATCH('Summary Sheet'!$A$" & ii & ",'Data Sheet'!$A:$A,1))"
        
        Worksheets("Summary Sheet").Names.Add Name:=RangeName, RefersTo:=namedref
        
        i = i + 1
        ii = ii + 1
        
    Next cell
    
    'Adds named ranges to workbook in order to create sparklines
    
    Range("L1").Select
    Selection.ListNames
    Range("L1").Select
    
    'Pickes up named ranges
    Set DataRange = Worksheets("Summary Sheet").Range("L1", ActiveSheet.Range("L1").End(xlDown))


    'Loop through each named range in created list and stores to a string
    For Each cell In DataRange.Cells
        myString = myString & "," & cell.Value
    Next cell


    'Remove first delimiter from string (;|;)
    myString = Right(myString, Len(myString) - 1)
    
    'Creates sparklines group for all names
    srcrng.Offset(ColumnOffset:=1).SparklineGroups.Add Type:=xlSparkLine, SourceData:=myString
    
    'Clears contents from L:M (named ranges printed in workbook -no longer required)
    Columns("L:M").ClearContents
    
    Range("A1").Select
    
    MsgBox "Sparklines Created."
    
End Sub