Set Source Data for Chart On Save

[laxstar785]

Hello,

I am having an issue when trying to run a macro on save, I am trying to set the source data for a chart on a specific worksheet. I do not want to use activate because I do not want excel changing to that sheet to update the chart and taking me away from the current sheet I am on. here is the snippet of my code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)​

​

Dim LR As Integer​

​

'Find last cell in column A with a date​

LR = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row​

​

If Sheets("Sheet1").Cells(LR, 1).Value = Date Then​

Sheets("Sheet1").Cells(LR, 2) = Application.WorksheetFunction.CountIf(Sheets("Micro 3in").Range("$E$3:$E$1998"), "Open")​

Sheets("Sheet1").Cells(LR, 3) = Application.WorksheetFunction.CountIf(Sheets("Micro 3in").Range("$E$3:$E$1998"), "Pending")​

Sheets("Sheet1").Cells(LR, 4) = Application.WorksheetFunction.CountIf(Sheets("Micro 3in").Range("$E$3:$E$1998"), "Complete Needs ECO")​

Sheets("Sheet1").Cells(LR, 5) = Application.WorksheetFunction.CountIf(Sheets("Micro 3in").Range("$E$3:$E$1998"), "Waiting For Samples")​

Else​

​

'Insert Date and count for conidtions in next row​

Sheets("Sheet1").Cells(LR + 1, 1).Value = Date​

Sheets("Sheet1").Cells(LR + 1, 2) = Application.WorksheetFunction.CountIf(Sheets("Micro 3in").Range("$E$3:$E$1998"), "Open")​

Sheets("Sheet1").Cells(LR + 1, 3) = Application.WorksheetFunction.CountIf(Sheets("Micro 3in").Range("$E$3:$E$1998"), "Pending")​

Sheets("Sheet1").Cells(LR + 1, 4) = Application.WorksheetFunction.CountIf(Sheets("Micro 3in").Range("$E$3:$E$1998"), "Complete Needs ECO")​

Sheets("Sheet1").Cells(LR + 1, 5) = Application.WorksheetFunction.CountIf(Sheets("Micro 3in").Range("$E$3:$E$1998"), "Waiting For Samples")​

​

Sheets("Sheet1").ChartObjects(1).Chart.SetSourceData Source:=Sheets("Sheet1").Range(Cells(1, 1), Cells(LR + 1, 5))​

End If​

​

End Sub​

This is the line that is giving me an error:

Sheets("Sheet1").ChartObjects(1).Chart.SetSourceData Source:=Sheets("Sheet1").Range(Cells(1, 1), Cells(LR + 1, 5))​

It doesn't give me an error if I am on Sheet1, it works correctly, but as soon as I am on another sheet it spits the error. If I change it to this:

Sheets("Sheet1").ChartObjects(1).Chart.SetSourceData Source:=Range(Cells(1, 1), Cells(LR + 1, 5))
​

this works but since I am not defining the work sheet it changes the source data to whatever sheet I am currently on which isn't good.

I am looking for some clarification on how to properly call out a worksheet in the setsourcedata command.

 

[laxstar785]

As an update, I have been testing it more and the code appears to run correctly, it selects the chart and reselects the source data. But I still get runtime 1004 error every time I run the code while not on Sheet 1 using this:

Sheets("Sheet1").ChartObjects(1).Chart.SetSourceData Source:=Sheets("Sheet1").Range(Cells(1, 1), Cells(LR + 1, 5))