Ted Murphy
New member
It is not possible to insert a PowerPivotChart into a worksheet in Excel 2013 without opening the Data Model window, which can be intimidating for an end user consumer.
The only alternative is to create a VBA macro that will perform the task.
Matt Allington, with assistance from Dominik Petri, published the script below to create a insert a PowerPivotTable. It work a treat.
I have tried to create a variation that would allow a PowerPivotChart to be inserted. However, my VBA skills don't stretch that far and I came unstuck.
Does anyone have the time (and capability) to modify the Macro so that it will work for a Chart?
Any help would be much appreciated. The macro will be very useful to anyone using PowerPivot in Excel 2013.
Macro for Pivot Table as published by Matt Allington (with input from Dominik Petri)
The only alternative is to create a VBA macro that will perform the task.
Matt Allington, with assistance from Dominik Petri, published the script below to create a insert a PowerPivotTable. It work a treat.
I have tried to create a variation that would allow a PowerPivotChart to be inserted. However, my VBA skills don't stretch that far and I came unstuck.
Does anyone have the time (and capability) to modify the Macro so that it will work for a Chart?
Any help would be much appreciated. The macro will be very useful to anyone using PowerPivot in Excel 2013.
Macro for Pivot Table as published by Matt Allington (with input from Dominik Petri)
Code:
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
Last edited by a moderator: