Generating sparklines in different worksheet from data.


New Member
Jan 6, 2015
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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
    Wk = ActiveCell.Value
    'Determine start and end columns for 10 weeks
    StartCol = Wk - 6
    EndCol = Wk + 3
    'Select sheet to show Spark lines
    'Set Sparkline Range  
    Set Rng1 = Sheets("Sheet1").Range("P2:Y41")
    ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng1
    'Create the sparklines.
    Range("$B$1:$B$40").SparklineGroups.Add Type:=xlSparkLine, SourceData:= _
    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 = - _
    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 = _
    Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.TintAndShade = _
    ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Last edited:
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back