I have a pie chart and the source data moves 1 space over to the right every month as I add a new column to the left of the data. The data is names along with percents. Arranged like this:

Cash Stocks Bonds
20% 45& 25%

There is a cell called "Current Mo.", which is in the previous column and 1 row up from the word "cash". The active sheet is called "detail" and the piechart is the top chart along with 3 others in a sheet called "graph"

I tried to figure out how to do this using the macro recorder, but am getting an error. Here's what I have:



Sub Macro2()
'
' Macro2 Macro
'

'
Dim PieChartValues As Range

Cells.Find(What:="current mo.").Activate
Set PieChartValues = ActiveCell.Offset(1, 1).Range("A1:E2")

With Sheets("Graph")
    .ChartObjects("Chart 5").Activate
    .ChartObjects("Chart 5").Activate
    .SetSourceData Source:=Sheets("Detail").Range(PieChartValues)
End With

End Sub