Hello,

I'm relatively new to VBA and updating pie charts has proven to be a big challenge.

The monthly report I create consists of approximately 120 pie charts and the number of slices in the charts can change from one month to the next. I refuse to do each one manually.

I have vlookup formulas set up that return volume results used in pie charts. I currently have the formulas set up to return a "blank" if the result is an error. My problem is I can't figure out how to find the last cell with data.

Code 1 below updates the pie chart, but only if I delete the formulas returning blanks.

Code 2 finds the last cell with data. The issue with Code 2 is I can't figure out how to implement it with Code 1. Code 3 below is my attempt to get this to work, but experienced coders will probably get a chuckle.

Please help. We are on a tight timeline and the deliverable is drawing near.

Thank you in advance





Code 1
Sub AGraphSeries2a()
ActiveSheet.ChartObjects("Chart 6").Activate
With Sheets("LVL1 GRAPH DATA Site")
ActiveChart.SeriesCollection(1).Values = _
.Range("G46:G" & .Range("G59" & ActiveCell).End(xlUp).Row)
End With
End Sub

Code 2
Range("G46:G60").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select


Code 3
Sub Find()
'
Dim st As Long
Dim ed As Long
Dim sh As Worksheet
Dim ws As Worksheet

Set sh = Sheets("LVL1 GRAPH DATA Site")
Set ws = Sheets("TBC Phone Graphs Roll _Temp")


st = sh.Range("G46").Activate
ed = Sheets("LVL1 GRAPH DATA Site").Range("G46:G60").Activate
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select

With sh

Range(("G46" & ":G" & 46), ("G" & ed & ":G" & ed)).Activate
End With


End Sub