M
Michael C
(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:=xlDatabase,
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.
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:=xlDatabase,
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.