Generating sparklines in different worksheet from data.

[theohall]

I am adding sparklines to help show trends easily. However, the client does not want to see all of the data next to the sparklines. Is there a way to specify to show the sparklines in a separate sheet from where the data is stored.

ie. data is in Sheet1. Show the data in Sheet2.

This is the base code for creating the sparklines in the Active sheet.

'Create the sparklines.

    Range(Cells(2, LastCol+1), Cells(LastRow, LastCol+1)).Select
    Selection.SparklineGroups.Add Type:=xlSparkLine, _
    SourceData:=Range(Cells(2, 2), Cells(LastRow, LastCol)).Address

 

[theohall]

Solved this by using named ranges, however, the Named range needs to be dynamic based on week selected (Wk). The variable Wk is then changed to StartCol and EndCol for the start and end columns. The beginning row and end row are always 2 and 41, respectively.

The question - how to change this:

Set Rng1 = Sheets("Sheet1").Range("P2:Y41")

into code using Row 2 and 41 for the range with starting and ending columns of StartCol and EndCol.

Here is the full code:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+l
'
    Dim Rng1                    As Range
    Dim Wk                      As Integer
    Dim StartCol                As Integer
    Dim EndCol                  As Integer
        
    'Select the most recent week completed
    Sheets("Sheet1").Select
    Range("C1").Select
    Wk = ActiveCell.Value
    
    'Determine start and end columns for 10 weeks
    StartCol = Wk - 6
    EndCol = Wk + 3
    
    'Select sheet to show Spark lines
    Sheets("Sheet2").Select
        
    'Set Sparkline Range  
    Set Rng1 = Sheets("Sheet1").Range("P2:Y41")
        
    ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng1
    
    'Create the sparklines.
    ActiveCell.Range("B1:B40").Select
    Range("$B$1:$B$40").SparklineGroups.Add Type:=xlSparkLine, SourceData:= _
        "MyRange"
    Selection.SparklineGroups.Item(1).SeriesColor.ThemeColor = 5
    Selection.SparklineGroups.Item(1).SeriesColor.TintAndShade = -0.499984740745262
    Selection.SparklineGroups.Item(1).Points.Negative.Color.ThemeColor = 6
    Selection.SparklineGroups.Item(1).Points.Negative.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Markers.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Markers.Color.TintAndShade = - _
        0.499984740745262
    Selection.SparklineGroups.Item(1).Points.Highpoint.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Highpoint.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Lowpoint.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Lowpoint.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Firstpoint.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Firstpoint.Color.TintAndShade = _
        0.399975585192419
    Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.TintAndShade = _
        0.399975585192419
    ActiveCell.Offset(0, -1).Range("A1").Select
End Sub