Posts by JaneP

    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)