Excel VBA To add new chart source column monthly in Loop for multiple charrt sheet

In Excel VBA I am updating a grid of data and the related charts.

I have a chart sheet and a data sheet.
There are many charts on the chart sheet and Each month I need to adjust the source from my data sheet "+1 column"
Id like to loop for each chart on sheet XYZ where I select range and add 1 new column
Mitch SwetskyBusiness AnalystAsked:
Who is Participating?
Rob HensonFinance AnalystCommented:
With my suggestion, setting the chart range to be the complete range required, ie 12 columns/rows for a 12 month period, would be a one off exercise. Once the data is updated in the existing range the new data will automatically show on the charts.

You could maybe then just have VBA to unhide the next column each time a change is required.
Rob HensonFinance AnalystCommented:
Can you upload a sample file rather than screenshots?
Mitch SwetskyBusiness AnalystAuthor Commented:
I've uploaded a sample but cant Copy more Because its private corporate data.
The overall charts sheet has more than 20 Graphs and the data has 122 rows
Each month a new column of data gets added and the chart needs to update by the VBA macro too.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob HensonFinance AnalystCommented:
I haven't had chance to look at the file yet but thought I would just let you know about a couple of simple tricks that might fix your issue.

Set the data range to the full extent required, eg 12 columns for a 12 month period, eventhough some of those columns are not yet populated. if you then hide the columns that are not populated they will not show in the graph. So in future months you just unhide the columns one at a time and the relevant data will then appear in the chart.

Alternatively, with the data range include a spare blank column. That blank column is shown on the graph but just appears as an extra blank section on the right hand end of the axis, inserting a column before the blank column for the new month's data will automatically expand the data range.

Rob H
Mitch SwetskyBusiness AnalystAuthor Commented:
Thanks very much. I tried a few things including your suggestions and creating a table but I will be handing off the completed macro enabled book and would like it to be dynamic.
Mitch SwetskyBusiness AnalystAuthor Commented:
I am looking for an answer to my original question. Does anyone have a solution??

I have the data updated using offset and paste statements but need the charts updated.
Mitch SwetskyBusiness AnalystAuthor Commented:
Although the solution was not what I wanted in VBA it would work.
I obtained agreement from the customer to only show 13 months.
I have amended the source to output from the database.

'Column S has the dates of the records and I copy those dates into my List sheet
    Application.CutCopyMode = False
'Removing Duplicates leaves me with unique date values which I have formatted as "mmmyy"
    ActiveSheet.Range("$J$1:$J" & LastRowF).RemoveDuplicates Columns:=1, Header:= _
    Range(Selection, Selection.End(xlDown)).Select

' I Copy the 13 months into Header rows for data in summary
     Sheets("Partner Summary").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

Thank you for your efforts and sticking with this
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.