VBA to Insert a Power Pivot Table

When Power Pivot was originally released by Microsoft, it was introduced as a Plug-In to Excel 2010.  After the second version of the Plug-In was built and released, the Microsoft Build team moved its focus to writing the product into the 2013 release of MS Office.  Unfortunately they ran out of time to write in all the features into the Office 2013 version – consequently there are a number of features that are actually better in Excel 2010 plug in version than the Office 2013 version.

In 2010, it is easier to edit your measures

In Excel 2010 you can right click on a measure in the Field List Window and select “EDIT”.  In 2013 you have to open the Manage Calculated Fields dialog and then find the measure you want to edit.

And 2010 is easier to insert a Power Pivot Table

In Excel 2010 there is an “insert pivot table” button on the Power Pivot tab in Excel.  Just click on this button to create a new Pivot Table that is connected to the data model.  No such button exists in Excel 2013 (unless you go into the Power Pivot window.

The alternative to navigating to the Power Pivot window is you have to use 8 clicks to insert a table (Shown below).

The Solution – Some VBA to Add a Pivot Table

I have been thinking of writing some VBA code to replace this missing insert pivot table connected to the data model button from Excel 2013, and I got to it last week.  It is a very small amount of code that makes the process very easy.  Just copy the code into your personal.xlb workbook and then add a shortcut to the code in your quick launch menu.  You can then use this VBA to Insert a Power Pivot Table into any existing worksheet.

Edit: This was my original code

Sub InsertPPTable()
'
 'capture the starting cursor location
 myAddress = ActiveCell.Address
 myAddress = ActiveCell.Address
 X = ActiveWorkbook.PivotCaches.Count

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
 ActiveWorkbook.Connections("ThisWorkbookDataModel")).CreatePivotTable TableDestination:=Range(myAddress)
 Cells(1, 1).Select
 Range(myAddress).Select
End Sub

Improved code from Dominik

(see comments section for background)

Sub InsertPowerPivotTable()
   Dim PowerPivotCache As Excel.PivotCache
   Dim NewPowerPivotTable As Excel.PivotTable

   ‘ Create new cache
   Set PowerPivotCache = ActiveWorkbook.PivotCaches.Create( _
   SourceType:=xlExternal, _
   SourceData:=ActiveWorkbook.Connections(“ThisWorkbookDataModel”), _
   Version:=xlPivotTableVersion15)

   ‘ Create PivotTable
   Set NewPowerPivotTable = PowerPivotCache.CreatePivotTable( _
   TableDestination:=ActiveCell, _
   DefaultVersion:=xlPivotTableVersion15)

   ‘ Settings for new PowerPivotTable
   With NewPowerPivotTable
     .RowAxisLayout xlTabularRow
     .HasAutoFormat = False
   End With
   
   ‘ Cleanup
   Set NewPowerPivotTable = Nothing
   Set PowerPivotCache = Nothing
End Sub

Here is what it looks like to insert a Pivot Table linked to the data model when using my VBA code.