title: Copying PivotTable Values in VBA
body: <p>I have created a pivot table via macro and want to copy the pivot table as values using VBA. Creating a Pivot Table went well but copying it to another sheet gives me a headache. Here's the code:</p>
  
  <pre><code>Sub test()
  Dim shtTarget, pvtSht As Worksheet
  Dim pc As PivotCache
  Dim pt As PivotTable
  Dim field As PivotField
  Dim rngSource As Range
  
  With ActiveWorkbook
      Set rngSource = .Sheets(2).Range("H:I").CurrentRegion
      Set shtTarget = .Sheets.Add(After:=.Sheets(.Sheets.count))
      shtTarget.Name = "Temp"
      Set pc = .PivotCaches.Create(xlDatabase, rngSource, xlPivotTableVersion14)
      Set pt = pc.CreatePivotTable(shtTarget.Range("A1"), "PivotTable1", , xlPivotTableVersion14)
  
  End With
  
  With shtTarget.PivotTables("PivotTable1").PivotFields("Concatenate")
      .Orientation = xlRowField
      .Position = 1
  End With
  
  With shtTarget
      .PivotTables("PivotTable1").AddDataField .PivotTables( _
      "PivotTable1").PivotFields("SCREEN_ENTRY_VALUE"), "Count of SCREEN_ENTRY_VALUE" _
      , xlSum
  End With
  
  With ActiveWorkbook
      Set pvtSht = .Sheets.Add(After:=.Sheets(.Sheets.count))
      pvtSht.Name = "Sum of Element Entries"
  
  '==========================I'm stuck on this line=================================
  
      .Sheets(shtTarget).PivotTables("PivotTable1").TableRange1.Copy 
      pvtSht.Range("A1").PasteSpecial xlPasteValues
  End With
  
  End Sub
  </code></pre>
  
  <p>The error is a <code>Type mismatch</code> error.</p>