VBA to change source data of charts by sheetname

Hi everyone,

I’m new in VBA, trying to find out solutions to my problems bymyself. But below is something I couldn’t make any progress.
I have a monthly report file where I store input data in sheets named Jan, Feb etc. I produce the reports on separate sheets and name them as Jan Report ,Feb report as you may guess. I have 6-7 charts in report sheet where source data is from input sheets. When I start a report for new month, I simply copy and rename last month’s report sheet and work on it. However, all charts still refers to last month’s data and I’m updating source data of each series in each chart manually. This means about 50 link to be updated. I tried to find out a solution by writing something below. All I want to change sheet name in source data for next month.

Please Login or Register  to view this content.

By the way, when I copy the sheet and rename fit for new report generation, chart names are changed in new sheet, so the SeriesCollection function will not help?. I need a code to cover all chart objects in the active sheet.

Thanks for your help.
Regards
Antak

I just tested this scenario.

Sheet with chart data called Jan. Sheet with chart object, linked to Jan, called JanReport.
I select both Jan and JanReport sheet tabs. Used the Move/Copy dialog to copy the 2 sheets.
Renamed Jan (2) to Feb.
Renamed JanReport (2) to FebReport.

FebReport was linked to Feb sheet.

If you copy each sheet separately then the references will not update automatically.