How Do I Quickly Re-filter and Export My Pivot Table Views to PowerPoint Slides?

Can you also make PowerPoints of your analyses for us, so that we can directly use them for our meetings?

As much as I like getting new responsibilities, it would be a stretch to say I was jumping with joy at the prospect of spending a good hour or two on the mind-numbing activity of making screenshots of my Excels and pasting them in PowerPoint.

So, I went about seeking a way of doing it quickly and efficiently. Naturally, automation was the first approach that came to mind.

If you’re facing a similar hurdle, don’t panic – there’s a neat way around it!

You can easily use Excel’s VBA environment to copy your Excel content, call the PowerPoint application and paste it there.

My task at hand involved re-filtering the pivot table placed on a particular sheet about 10 times and copying its content after each re-fitlering on a set slide in a pre-defined PowerPoint presentation.

The code below does exactly this.

Sub Utilization()
'written by Angelina Teneva, 2013
Application.Calculation = xlCalculationAutomatic
Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem
Dim L As String
Dim PL As String
Dim Sh As Shape
Dim PPApp As PowerPoint.Application
Dim PPpres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
'Create a PP application and make it visible
Set PPApp = New PowerPoint.Application
PPApp.Visible = msoCTrue
'Open the presentation you wish to copy to
Set PPpres = PPApp.Presentations.Open("C:\Users\Angelina\Desktop\Utilization.pptm")
'prevent PowerPoint 2013 from losing focus and returning
'"shapes (unknown member) invalid request. the specified data type is unavailable"
'- Run-time error -2147188160 (80048240):View (unknown member) error
PPApp.ActiveWindow.ViewType = ppViewNormal
PPApp.ActiveWindow.Panes(2).Activate 'standard ppt view
'get Delivery Pillar Utilization
Set PT = Worksheets("Utilization").PivotTables("PivotTable1")
PT.PivotFields("Pillar").PivotFilters.Add Type:=xlCaptionEquals, Value1:="Delivery"
For Each PT In ActiveSheet.PivotTables
Set PF = PT.PivotFields("Subregion ")
For Each PI In PF.PivotItems
L = PI.Value
PF.CurrentPage = L
PT.PivotSelect "", xlDataAndLabel, True
Select Case L
Case "CEE": PPpres.Slides(2).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "FRA": PPpres.Slides(11).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "GER": PPpres.Slides(20).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "GWE": PPpres.Slides(29).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "IBE": PPpres.Slides(38).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "ITA": PPpres.Slides(47).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "MEMA": PPpres.Slides(56).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "UKI": PPpres.Slides(65).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "RUS": PPpres.Slides(73).Shapes.PasteSpecial ppPasteEnhancedMetafile
End Select
Next PI
Next PT
End Sub

Naturally, because it was developed for such a specific case, most of it will have to be re-written to fit the requirements of any other scenario. What is directly transposable, however, is the bit that calls the PowerPoint environment in the beginning.

Most importantly, you need to make sure that the Microsoft PowerPoint Object Library is invocable from Excel by enabling the reference in Excel VBE Editor

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.