VBA to create 2 pivot pie charts with a dynamic range

grtct

New Member
Joined
Dec 12, 2017
Messages
4
Hello,

I'm new here and I was trying to write a macro that can create 2 pivot pie charts with a dynamic data range.
What I did was to record the macros to create a pie chart with connection only, then did some google searches how to express a dynamic data range. Unfortunately I have no code background... And when merging the codes I faced a problem.

Error line:
'Create 1st pivot chart: order category
With pvcache.CreatePivotChart(ChartDestination:="TR")
End With

----

My target is:
- create 2 pie charts showing categories (4 categories) and stores (10 stores) by means of 1 common items
- those pie charts have 1 dynamic data range. After new inputs, I suppose the charts will change automatically
- those charts are placed in 2 fixed boxes.

Please help me complete the code.

Thank you.


----

Please see the incomplete merged code as follows:

Code:
Dim pvcache As PivotCache

Dim pvfirstrow As Long
Dim pvlastrow As Long
Dim pvfirstcol As Long
Dim pvlastcol As Long

Dim pvsourcedata As String

Dim pvsourcews As Worksheet
Set pvsourcews = ThisWorkbook.Sheets("TR")


pvfirstrow = 19
pvlastrow = Sheets("TR").Cells(Rows.Count, 8).End(xlUp).Row
pvfirstcol = 1
pvlastcol = Sheets("TR").Cells(19, Columns.Count).End(xlToLeft).Column


With pvsourcews.Cells
    pvsourcedata = Range(.Cells(pvfirstrow, pvfirstcol), .Cells(pvlastrow, pvlastcol)).Address
End With


'Create pivot cache
Set pvcache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pvsourcedata, Version:=6)

'Create 1st pivot chart: order category
With pvcache.CreatePivotChart(ChartDestination:="TR")
End With


With ActiveChart.PivotLayout.PivotTable.CubeFields("[Range].[Order Cat]")
    .Orientation = xlRowField
    .Position = 1
End With
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.CubeFields("[Measures].[Sum of Work in hours]"), _
        "Sum of Work in hours"
    ActiveChart.ChartType = xlPie
    ActiveChart.ShowAllFieldButtons = False
    ActiveChart.ApplyLayout (7)
    ActiveChart.SetElement (msoElementChartTitleNone)


'Create 2nd pivot chart: store

With ActiveChart.PivotLayout.PivotTable.CubeFields("[Range].[store]")
    .Orientation = xlRowField
    .Position = 1
End With
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.CubeFields("[Measures].[Sum of Work in hours]"), _
        "Sum of Work in hours"
    ActiveChart.ChartType = xlPie
    ActiveChart.ShowAllFieldButtons = False
    ActiveChart.ApplyLayout (7)
    ActiveChart.SetElement (msoElementChartTitleNone)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top