Add a PivotTable using VBA and adding to the Data Model

[Confidentjohn]

Hi

I want to set a pivottable and also use the DataModel feature (Excel 2013)

If I record the VBA I get the code I need however it is set to the specific sheet

Sub Macro4()'
' Macro4 Macro
'


'
    Workbooks("April 2015 - orderDATA.xlsx").Connections.Add2 _
        "WorksheetConnection_Sheet1!$A$1:$AC$129180", "", _
        "WORKSHEET;C:\Users\jmitchel\My Docs\[April 2015 - orderDATA.xlsx]Sheet1" _
        , "Sheet1!$A$1:$AC$129180", 7, True, False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("WorksheetConnection_Sheet1!$A$1:$AC$129180"), _
        Version:=xlPivotTableVersion15).CreatePivotTable TableDestination:= _
        "Sheet3!R3C1", TableName:="PivotTable2", DefaultVersion:= _
        xlPivotTableVersion15
    Sheets("Sheet3").Select
    Cells(3, 1).Select
End Sub

I would like to reuse this Macro for other documents I have so ideally i would like to remove specific references to the doc and just have the macro run against the active sheet, Could anyone help me edit the code?

Cheers

Joh

 

[sararose27]

Did you figure this out? I"m trying to do the same thing.