VBA - Sparkline SourceData to reference another Sheet using Range(Cells method

[goodysgotacuda]

Currently I have a pre-existing sparkline that references data from another sheet. Manually, this process works just fine, however I am strugging to convert the process to vba.

I need to convert it to VBA because my #of rows changes and it needs to recognize that on it's own.


I have the following code that I am using to try to reference the changing #of rows, on another sheet. This code within the "Temperature" sheet works fine when I remove the "Sheets("Temperature"). portion of the code.




I am looking to see if I can add the sheet reference to the range, somehow similary to how I have it below. The code shown errors out.

Sheets("Temperature").select

   With ActiveSheet
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        LastColumn = .Cells(2, .Columns.Count).End(xlToLeft).Column
    End With

Sheets("Results").select

    Range("B4:J12").Select
    ActiveCell.SparklineGroups.Item(1).Modify Location:=Range("$B$4"), _
        SourceData:=Sheets("Temperature").Range(Cells(2, 2), Cells(LastRow, 2)).Address
    Range("B4:J12").Select
    Selection.End(xlUp).Select