(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.

Instead of ActiveSheet, use a reference to Sheet2. For example:

'=========================
Dim ws2 As Worksheet
Set ws2 = Worksheets("Sheet2")
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:="Sheet1!R3C2:R10C4").CreatePivotTable _
TableDestination:=ws2.Name & "!R19C2", _
TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
ws2.PivotTables("PivotTable2").AddFields _
RowFields:="group", ColumnFields:="amr"

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

Michael C wrote:
(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.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

On May 14, 5:34 am, Debra Dalgleish wrote:
Instead of ActiveSheet, use a reference to Sheet2. For example:

'=========================
Dim ws2 As Worksheet
Set ws2 = Worksheets("Sheet2")
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:="Sheet1!R3C2:R10C4").CreatePivotTable _
TableDestination:=ws2.Name & "!R19C2", _
TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
ws2.PivotTables("PivotTable2").AddFields _
RowFields:="group", ColumnFields:="amr"

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



Michael C wrote:
(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.

--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html

Thanks. I will try this.

-Mike