type mismatch trying to create pivot table with extermal connection/data model

  • Have a macro creating pivot tables using a table as source which works ok. Now trying to do same where source is table that has already been added to data model and so far i cant get past first step. I get type mismatch - variables work fine on table version. Any help in what is wrong greatly appreciated and help on using variables for source data as well would be good to as its part of a bigger scheme to add many many pivot tables.

    Dim InsertPos As Range
    CurrRow = ActiveCell.Row
    fixedCol = ActiveCell.Column

    sparestring = "R" & CurrRow
    Set InsertPos = Range(sparestring)

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal, SourceData:= _
    ActiveWorkbook.Connections("WorksheetConnection_outcomesPIVOTS.xlsm!outcomesterm")) _
    .CreatePivotTable TableDestination:=InsertPos, _
    TableName:=sCustomList(i, PtNamePos)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!