Select Pivot chart option using VBA 1

[ecompa]

Hi,
I have a Main report in an Excel sheet with several pivot tables and pivot chart where common dimensions are available.
To prepare a summary of some combinations of Product and Markets, I need a macro that will select options in the different pivot charts, makes a copy in a blank sheet and print it. The option will be in an Access table, and the code will run based on a query on this table in Access VBA.

At this stage, the code I prepared works one time, then blocks if want to re-run it (at the "ActiveChart line of the first procedure). As the idea is to make a loop on several combinations of Product and Market this is a problem.

Here is an extract of the code

Public Proc1
Set wb = app.Workbooks.Open("MainReport.xls")
    
strProduct = "Abc"
strMarCurr = "(All)"

Set ws = wb.Sheets("ChartDepot")
ws.Activate
ws.ChartObjects("Chart 1").Activate
With ActiveChart.PivotLayout.PivotTable
    .PivotFields("Product ").CurrentPage = strProduct
    .PivotFields("Market").CurrentPage = strMarCurr
    .PivotFields("Segment").Orientation = xlPageField
    .PivotFields("Segment").Position = 1
    .PivotFields("Market").Orientation = xlColumnField
    .PivotFields("Market").AutoSort xlDescending, "Data"
End With

Call WriteDepotChart
End Sub

Public Sub WriteDepotChart()
    
'opens a blank workbook
Set wb2 = app.Workbooks.Add
wb2.SaveAs ("Abc_All.xls")

Set ws2 = wb2.Sheets("Sheet1")

'copy Data Chart
    wb.ActiveChart.ChartArea.Copy
    'wb.ActiveWindow.Visible = False
    ws2.Range("A3").Select
    ws2.Paste
    
'format Data Chart
    ws2.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    Selection.Width = 577
    ActiveChart.Legend.Left = 598
    ActiveChart.Legend.Width = 68
    ActiveChart.ChartArea.Select
    ws2.DrawingObjects("Chart 1").Placement = xlFreeFloating
    ws2.DrawingObjects("Chart 1").PrintObject = True
    ws2.DrawingObjects("Chart 1").Locked = True
    
End Sub

I don't know why it is not working more tha one time (I have to close the application and restart everything, then it works again).
Is it due to the fact that I use two Workbook objects (wb and wb2) and that each of them have an "ActiveChart" during the process ? If this is the case how could I reinitiate everything at the end of the process so that new values for variable "Product" and Market" could work ?

Any suggestion is welcome. Maybe there is another (easier) way to reach the same result ...
Thanks
Ecompa

 

[PHV]

Replace all non qualified references to ActiveChart with app.ActiveChart

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ecompa]

right to the point. Excellent.
Many thanks !!