I am trying to automatically update a chart using VBA whenever user
clicks on the worksheet.

Excel 2003 SP2 executes a SQL stored procedure and gets data from SQL
Server backend - about some project categories on a monthly basis. And
hence the each month a new column is added to the dataset for all
categories (rows). The previous months are not deleted, they are needed
as well. But the chart only shows the columns that were specified when
the chart was created and user has to manually right click and change
the "data range" in "Source Data" dialog box to include the new column.


After some research on google I found a formula that works:

=OFFSET(Call_Category_Monthly!$A$1,0,0,
COUNTA(Call_Category_Monthly!$A:$A),
COUNTA(Call_Category_Monthly!$1:$1))


but when I put in the formula in the data range text box on the "Source
Data" window, it changes to the actual column and row values:
=Call_Category_Monthly!$A$1:$K$7

I want to include some VBA code to update the chart and include the
newly added column automatically.

I recorded a macro while changing the data range, but that also gets
recorded as the actual values not what I enter (which is the OFFSET
function):

ActiveChart.SetSourceData
Source:=Sheets("Call_Category_Monthly").Range("A1: K7"), PlotBy:=xlRows

I do not know how to change the Range("A1:K7") to OFFSET / COUNTA
function. I tried a couple of times, but it failed.

I am a SQL Server DBA/Developer and new to Excel and VBA.

Any help will be greatly appreciated.