I've been trying to create a macro that will create a pivot table
using an existing data matrix. I keep getting an error message as
follows: "Unable to get the PivotTables properties of the Worksheet
class" Here is the coding where the error occurs:

Sheets("Details").Select
Range("A1").Select
Selection.CurrentRegion.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Details!R1C1:R8822C23").CreatePivotTable TableDestination:= _
"'[Import profile macros.xls]Pivotx'!R1C1",
TableName:="PivotTable14", _
DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable14").PivotField s("Shipper
Name")
.Orientation = xlRowField
.Position = 1
End With

The error seems to occur on the With Activesheet...... line.

It seems that the macro can't locate the sheet where the pivot table
resides. This problem occurs whether I place the pivot table on a new
sheet or on an existing sheet. I have to admit that i am a neophyte
when it comes to writing Excel macros.

I appreciate any suggestions.

Try changing

With ActiveSheet.PivotTables("PivotTable14").PivotField s("Shipper
Name")

to

With Worksheets("pivotx").PivotTables("PivotTable14").P ivotFields("Shipper
Name")

since that was the destination of the PivotTable you added.

HTH,

wrote in message
oups.com...
I've been trying to create a macro that will create a pivot table
using an existing data matrix. I keep getting an error message as
follows: "Unable to get the PivotTables properties of the Worksheet
class" Here is the coding where the error occurs:

Sheets("Details").Select
Range("A1").Select
Selection.CurrentRegion.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Details!R1C1:R8822C23").CreatePivotTable TableDestination:= _
"'[Import profile macros.xls]Pivotx'!R1C1",
TableName:="PivotTable14", _
DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable14").PivotField s("Shipper
Name")
.Orientation = xlRowField
.Position = 1
End With

The error seems to occur on the With Activesheet...... line.

It seems that the macro can't locate the sheet where the pivot table
resides. This problem occurs whether I place the pivot table on a new
sheet or on an existing sheet. I have to admit that i am a neophyte
when it comes to writing Excel macros.

I appreciate any suggestions.

Many thanks for the suggestion! I made the change and was able to
advance further down the macro, but it now gets hung up on the
following statement:

End With
ActiveSheet.PivotTables("PivotTable14").AddDataFie ld
ActiveSheet.PivotTables( _
"PivotTable14").PivotFields("total cost for line item"), _
"Sum of total cost for line item", xlSum

I tried several variations to include ("pivotx") as was done earlier,
but none of these worked. Any suggestions?


On Mar 26, 4:06 pm, "George Nicholson"
wrote:
Try changing

With ActiveSheet.PivotTables("PivotTable14").PivotField s("Shipper
Name")

to

With Worksheets("pivotx").PivotTables("PivotTable14").P ivotFields("Shipper
Name")

since that was the destination of the PivotTable you added.

HTH,

wrote in message

oups.com...

I've been trying to create a macro that will create a pivot table
using an existing data matrix. I keep getting an error message as
follows: "Unable to get the PivotTables properties of the Worksheet
class" Here is the coding where the error occurs:

Sheets("Details").Select
Range("A1").Select
Selection.CurrentRegion.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Details!R1C1:R8822C23").CreatePivotTable TableDestination:= _
"'[Import profile macros.xls]Pivotx'!R1C1",
TableName:="PivotTable14", _
DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable14").PivotField s("Shipper
Name")
.Orientation = xlRowField
.Position = 1
End With

The error seems to occur on the With Activesheet...... line.

It seems that the macro can't locate the sheet where the pivot table
resides. This problem occurs whether I place the pivot table on a new
sheet or on an existing sheet. I have to admit that i am a neophyte
when it comes to writing Excel macros.

I appreciate any suggestions.