title: Recorded VBA macro to create a PivotTable giving application-defined error
body: <p>A macro I recorded to create Pivot Table is giving me the following error: </p>
  
  <blockquote>
    <p>Run-time error 1004: Application-defined or object-defined error.</p>
  </blockquote>
  
  <p>I have deleted the relevant parts from the recorded macro as per <a href="https://support.microsoft.com/en-us/kb/940166" rel="nofollow">this Microsoft link</a>.  However, I still receive the same error. </p>
  
  <pre><code>Sub testmacro()
      ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
          ActiveWorkbook.Connections("WorksheetConnection_Sheet8!$A$1:$E$162"), Version _
          :=xlPivotTableVersion15).CreatePivotTable TableDestination:="", _
          TableName:="PivotTable27", DefaultVersion:=xlPivotTableVersion15
      With ActiveSheet.PivotTables("PivotTable27").CubeFields("[Range].[and not]")
          .Orientation = xlPageField
          .Position = 1
      End With
      With ActiveSheet.PivotTables("PivotTable27").CubeFields("[Range].[term]")
          .Orientation = xlRowField
          .Position = 1
      End With
      ActiveSheet.PivotTables("PivotTable27").AddDataField ActiveSheet.PivotTables( _
          "PivotTable27").CubeFields("[Measures].[Count of family 2]"), "Count of family"
      With ActiveSheet.PivotTables("PivotTable27").PivotFields( _
          "[Measures].[Count of family 2]")
          .Caption = "Distinct Count of family"
          .Function = xlDistinctCount
      End With
  End Sub
  </code></pre>
  
  <p>The debugger throws an error at the ActiveWorkbook.PivotCaches.Create line.  A new worksheet does open, but it is blank.  Thank you.  </p>