Pivot Charts losing formatting when new data entered

alex1stef2

New Member
Joined
Sep 6, 2017
Messages
2
So I currently have a bunch of hidden sheets with pivot tables on them, and a bunch of unhidden sheets with the corresponding pivot charts. I am querying SQL for a bunch of data, and then the pivot tables feed off this new data to create the charts. My current problem is that every time I run my code the formatting of the pivot charts is reset.


I have tried: `Application.SetDefaultChart FormatName:=0` or just manually setting the graph to a line graph to no avail; it always resets back to the clunky looking point based line graph.


What I am currently trying is:


Code:
    Sub ChartUpdate()
    For Each MySheet In ActiveWorkbook.Worksheets
      For Each MyChart In MySheet.ChartObjects
        MyChart.Activate
        
        MyChart.ChartType = xlLine
      
      Next MyChart
    Next MySheet
    
    ActiveSheet.Activate
    Application.EnableEvents = True
But this doesn't seem to recognise pivot charts as chart objects, as it loops through all the sheets without entering the second `For Each` statement.


Ideally I would prefer not to have to resort to fully reformatting the chart each time as I may have to fix axes etc, which is why the setting default would have been perfect. Otherwise, if this is not possible, any solution to get my second attempt to work would be really helpful!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,099,252
Messages
5,467,556
Members
406,543
Latest member
semoredhawk

This Week's Hot Topics

Top