Creating pivot table to Existing worksheet

[Malvaro]

Here's all the relevant information:

a) I was able to successfully create a pivot table, in a newly created
tab named "Sheet2". This pivot table is contained in A:E, was copied
and the duplicate of that pivot table pasted into I:M.

b) Next I attempted to generate a new pivot table, using a new data
range, to the "Existing worksheet" at P:V. When running the VBC code
for the new pivot table I get the error "Run Time 1004: Unable to get
the Pivot Table Property of the Worksheet class"

The broken VBA code is below:

Sheets("Raw Data").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Raw Data'!R2C1:R" & RowCount & "C54").CreatePivotTable
TableDestination:= _
"'[MBR Prep Work 1.1.xls]Sheet2'!R1C16", TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array( _
"Column1", "Column2", "Column3", "Column4", "Column5"), ColumnFields:=
_
"Month"
ActiveSheet.PivotTables("PivotTable3").PivotFields("Case
No").Orientation = _
xlDataField

Also, as a kinda-related question: Using VBA, can one create a new
pivot table using the data range of the previous / original pivot
table. The manual option that says something about "saving memory and
making workbook the smaller"? If so, can you point me in the direction
of an example somewhere?

Any help would be greatly appreciated!!!

 

[Malvaro]

I wonder if the target worksheet must be actually named, vs the default
Sheet1, Sheet2, Sheet3, etc etc

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Raw Data'!R2C1:R" & RowCount & "C54").CreatePivotTable
TableDestination:= _
[MBR Prep Work 1.1.xls]*_Sheet2_*'!R1C16

Would that actually make a difference?