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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.