Automating saving pivot charts as separate png files

[nwije]

Hi All,


I'm extremely new to Visual basic!


I'm trying to save about 260 charts from the one pivot chart(Chart1). In the end, I want one chart for each business in the pivot field "Outlet / Town".


I created a macro to do each one manually, but the businesses may change so this will not be practical in the future.

[pre]

Sheets("Chart").Select
ActiveSheet.PivotTables("PivotTable7").PivotFields("Outlet / Town"). _
CurrentPage = "Business clothes"
Sheets("Chart1").Select
ActiveChart.Export Filename:=ActiveWorkbook.Path & "Business clothes.png", Filtername:="PNG"

Sheets("Chart").Select
ActiveSheet.PivotTables("PivotTable7").PivotFields("Outlet / Town"). _
CurrentPage = "Business shoes"
Sheets("Chart1").Select
ActiveChart.Export Filename:=ActiveWorkbook.Path & "Business shoes.png", Filtername:="PNG"

How can I create a looping file (eg. using For each PivotFields). I tried the following without success:

Sub Proc1()

Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem

Set pvtTable = Worksheets("Chart").PivotTables("PivotTable7")
Set pvtField = pvtTable.PivotFields("Outlet / Town")

Sheets("Chart").Select
ActiveSheet.PivotTables("PivotTable7").PivotFields ("Outlet / Town")
For Each pvtItem In pvtTable.PivotFields("Outlet / Town").PivotItems
CurrentPage = "pvtItem"
Sheets("Chart1").Select
ActiveChart.Export Filename:=ActiveWorkbook.Path & "" & pvtItem & ".png", Filtername:="PNG"

Next
End Sub

[/pre]
Any help would be much appreciated

 

[Hui]

Nwije


Firstly, Welcome to the Chandoo.org Forums


Can you upload a sample file so we can have access to the data and pivot tables to help us assist you

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook