(This has been cross posted to the VBA group as well)

Hello,

I am just learning VBA and macros in Excel so forgive me if this is a
simple mistake. I am trying to automate a pivot table and have been
encountering an error. The error says:

"Unable to get the PivotTables property of the Worksheet class."

I have figured out that this only happens when I try to make the
destination of the pivot table a different sheet than the data sheet.
If I used the same sheet it works just fine. The data is set up:

Name Group Rating

Where rating is a number 0,1, or 2. I am trying to count how many
people have non zero ratings in a group. Here is the code that I
recorded:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/13/2008 by Michael C
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Sheet1!R3C2:R10C4").CreatePivotTable
TableDestination:="[Book1]Sheet1!R19C2" _
, TableName:="PivotTable2",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable2").AddFields
RowFields:="group", _
ColumnFields:="amr"

ActiveSheet.PivotTables("PivotTable2").PivotFields ("name").Orientation
= _
xlDataField
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("amr")
.PivotItems("0").Visible = False
.PivotItems("(blank)").Visible = False
End With
End Sub


If I changed the TableDestination assignment above and set it for a
different sheet, that is when I get the error. I have tried to insert
a statement such as:

Worksheets("Sheet2").activate

I put this in just before the active sheet statement. But that does
not seem to work. Can someone guide me as to where I am making my
mistake. I can make it work if it is created on the same sheet but
ideally, I like to create a new sheet.

Thanks for you help.

-MIke

P.S. In the code above, AMR is the rating field that I specified.