I have a dynamic range I am using to update a line chart sheet. I am trying to create a macro that will update the range every time new data is added to a log sheet, however I keep getting a Script Out of Range error. I have changed numerous pieces of the script, however this line continues to give me an error:
Set ChartSheet1 = ThisWorkbook.Worksheets("Sales").ChartObject(1).Chart
Could someone please tell me what I am doing wrong when trying to reference this chart page? Here is the complete script I am using:
Sub UpdateChartSheet()
Dim ChartSheet1 As Chart
Dim LastRow As Long
Set ChartSheet1 = ThisWorkbook.Worksheets("Sales").ChartObject(1).Chart
LastRow = getLastRow(ThisWorkbook.Worksheets("Tables"), 6)
With ChartSheet1
.SetSourceData Source:=ThisWorkbook.Worksheets("Tables").Range("F1:F" & LastRow & ", G1:G" & LastRow & ", H1:H" & LastRow & ", I1:I" & LastRow)
End With
End Sub
I really just want to add each new data point to this chart sheet as its added to the workbook. Thank you in advance for any help you can offer.