VBA, creating a pivot table on a new sheet

[aallaman]

Hi Everyone,

I am trying to get my VBA code to create a pivot table off of a defined pivotcache, and to open it in a new window.

I am working out of MrExcel's VBA book, but I just cannot get it to work.

Here is my code which is an adapted version of what's in the book. I am getting a Run-time error 'S': Invalid procedure call or argument error on the line in red.

Sub ErrorReportingCreatePivotTableTake1()
'
' ErrorReportingCreatePivotTableTake1 Macro
'
'

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Error_Reporting")

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)

' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:="Sheet1!R3C1", TableName:="PivotTable1")

End Sub


Thanks in advance for any suggestions!!!

Allen