Sarah has attended:
Excel VBA Intro Intermediate course
Autofit Pivot Graph in VBA
Hi, I am using data from different reports to create PDF reports for specific stakeholders based on the group they need to report on. I have combined all the data into one template workbook which can be updated on a monthly basis and recorded/written the code to filter/create PDFs for each stakeholder.
The only problem I have is that I cannot change the size of the pivot graph depending on the number of entries it is showing. Is there any way to add this to the code? Ideally the graph will expand (simple bar chart) to include all entries under a specific filter or get smaller where there are less options in the axis.
Thanks,
Sarah.
RE: Autofit Pivot Graph in VBA
Hi Sarah,
Thank you for the forum question.
What you want is not straight forward. You need to count number of entries (Pivot Items) and store the number in a variable. If number of entries is greater than maybe 300 then you want this graph size. In the example below I just recorded that I resized the graph and added the code to my decision code.
Sub ResizePivotGraph()
Dim iPTItems As Integer
iPTItems = ActiveSheet.PivotTables("MyPT"). _
PivotFields("Product").PivotItems.Count
If iPTItems <12 Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.Shapes("Chart 2"). _
ScaleWidth 0.6880585156, msoFalse, _
msoScaleFromTopLeft
ElseIf iPTItems <24 Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.Shapes("Chart 2"). _
ScaleWidth 2.06627018, msoFalse, _
msoScaleFromTopLeft
End If
End Sub
I hope that this will help you to do what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu
RE: Autofit Pivot Graph in VBA
Hi Jens,
Thanks so much for this response, it is really helpful!
I've come across two hiccups, firstly iPTItems is counting all items in the pivot table rather than just those showing when it is filtered, so instead of 35 it is reading a 860, is there anyway to count just the filtered items?
The second problem is that the Pivot Table and chart are on two different sheets so when I activate the chart the variable changes?
Thanks,
Sarah.
RE: Autofit Pivot Graph in VBA
Hi Sarah,
Sorry you are right it counts all items. This one is working for me
Sub ResizePivotGraph()
Dim iPTItems As Integer
Dim pf As PivotField
Dim pt As PivotTable
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables("MyPT")
Set pf = pt.PivotFields("Product") 'Product is the field name
Sheet("PivotTable").Select
With pt
For Each pi In pf.PivotItems
If pi.Visible = True Then
iPTItems = iPTItems + 1
End If
Next pi
End With
If iPTItems < 12 Then
Sheets("ChartSheetName").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1"). _
ScaleWidth 0.6880585156, msoFalse, _
msoScaleFromTopLeft
ElseIf iPTItems < 24 Then
Sheets("ChartSheetName").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1"). _
ScaleWidth 2.06627018, msoFalse, _
msoScaleFromTopLeft
End If
End Sub
I hope this will do the job for you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu
