VBA reference chart and data in separate worksheets

Hi, all

I have very limited, extremely limited, VBA knowledge. But I was given this assignment to update a budget workbook for our unit.

I have a workbook that uses VBA code (I didn't write the code) to remove a legend series from a stacked column chart when the data equals 0 for that series. The following code references the chart on the same worksheet as the data. But, I have the data in different worksheets. And they must remain separate. How do I rewrite the code so that it removes the legend option on a stacked column chart in a worksheet labeled UnitBudget but the data is in worksheet DataBudget. Also, the chart is the third chart on that worksheet UnitBudget. All are in the same workbook 2013BudgetWorkbook.

Sub ChangeLegend()

Dim lngIndex As Long
Dim lngLegendIndex As Long
Dim chtTemp As Chart

Set chtTemp = ActiveBook.ChartObjects(1).Chart
With chtTemp
.HasLegend = False
.HasLegend = True
.Legend.Position = xlLegendPositionRight
lngLegendIndex = .SeriesCollection.Count
For lngIndex = 1 To .SeriesCollection.Count
If Application.WorksheetFunction.Sum(.SeriesCollection(lngIndex).Values) = 0 Then
.Legend.LegendEntries(lngLegendIndex).Delete
End If
lngLegendIndex = lngLegendIndex - 1
Next
End With
End Sub

I would really appreciate any help.

Thanks

Doug.