Hello, I'm hoping for some help on a VBA query. I'm very new to this all, and would love some guidance.
I have a workbook which contains 40+ tabs/worksheets. Each tab corresponds to a subsurface well and contains 5 charts related to it. It is used as a monitoring file, so is auto updated when the new month's data is available.
I want to be able to format only the X Axis scale on every chart in the workbook according to the very first chart in the very first tab.
I’ve managed to come up with the following VBA code:
Sub UpdateChartsBasedOnMaster()
Dim wksCharts As Worksheet
Dim oChrtObj As ChartObject
Set wksCharts = Worksheets("IG105") '
With wksCharts.ChartObjects("Chart 1").Chart '
For Each oChrtObj In wksCharts.ChartObjects
If oChrtObj.Name <> .Parent.Name Then
oChrtObj.Chart.Axes(xlCategory).MinimumScale = .Axes(xlCategory).MinimumScale
oChrtObj.Chart.Axes(xlCategory).MaximumScale = .Axes(xlCategory).MaximumScale
End If
Next oChrtObj
End With
Set wksCharts = Nothing
End Sub
Obviously this only works for the tab titled “IG105” and then I usually end up opening the VBA editor and changing that name to the name of the next tab (e.g. IG128, IG24, IG26, etc) and doing it for each worksheet.
What do I need to change the code to in order to loop the whole workbook and change each X axis according to the X axis that I set on “Chart 1” in IG105?
Any information is greatly appreciated, and I thank you in advance for your time.
Bookmarks