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)