title: VBA for creating pivot table
body: <p>I am trying to use VBA to insert a pivot table.</p>
  
  <p>However, every time I try to run it I get an error: "<strong>Run-time error 13: Type mismatch</strong>" on the <em>Define Pivot Cache</em> portion.  </p>
  
  <p>I've tried changing the variable names as well as not using some of them, but I still get the same error. Am I missing something?</p>
  
  <pre><code>Sub PivotTableAdd()
  
      'Declare Variables
      Dim PSheet As Worksheet
      Dim DSheet As Worksheet
      Dim PCache As PivotCache
      Dim PTable As PivotTable
      Dim PRange As Range
      Dim LastRow As Long
      Dim LastCol As Long
  
      'Insert a New Blank Worksheet
      'On Error Resume Next
      Application.DisplayAlerts = False
      'Worksheets("Pivot").Delete
      Sheets.Add Before:=ActiveSheet
      ActiveSheet.Name = "Pivot"
      Application.DisplayAlerts = True
      Set PSheet = Worksheets("Pivot")
      Set DSheet = Worksheets("Report")
  
      'Define Data Range
      LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
      LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
      Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
  
      'Define Pivot Cache
      Set PCache = ActiveWorkbook.PivotCaches.Create _
      (SourceType:=xlDatabase, SourceData:=PRange). _
      CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
      TableName:="PivotTable")
  
      'Insert Blank Pivot Table
      Set PTable = PCache.CreatePivotTable _
      (TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")
  
  End Sub
  </code></pre>
  
  <p>Edit:
  I changed this line and got it to work:</p>
  
  <pre><code>Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange.Address(True, True, xlR1C1, True))
  </code></pre>