Hi all, I can't seem to figure out why this code is not working to create a pivot table. I'm using Excel 2013 and will be updating to 2019 soon.
When I get to the last line of code, I get Runtime Error 5: Invalid Procedure Call or Argument
When I get to the last line of code, I get Runtime Error 5: Invalid Procedure Call or Argument
VBA Code:
Sub Auto_Pivot()
Dim PBook As Workbook
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim DRange As String
Dim ShtName As String
Dim LastRow As Integer
Dim LastColumn As Integer
Sheets.Add Before:=ActiveSheet
Set PSheet = ActiveSheet
PSheet.Name = "Preparer Summary"
ShtName = "Data"
Set PBook = ThisWorkbook
Set DSheet = Worksheets(ShtName)
LastColumn = DSheet.Cells(7, DSheet.Columns.Count).End(xlToLeft).Column
LastRow = DSheet.Cells(DSheet.Rows.Count, "A").End(xlUp).Row
DRange = Range(Cells(6, 1), Cells(LastRow, LastColumn)).Address
'Define Pivot Cache
Set PCache = PBook.PivotCaches.Create(xlDatabase, ShtName & "!" & DRange, xlPivotTableVersion15)
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(2, 2), TableName:="PrepSumPivot")
End Sub