problem creating a pivot table using a macro

[vrzimmerm]

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:=xlDatabase,
SourceData:= _
"Details!R1C1:R8822C23").CreatePivotTable TableDestination:= _
"'[Import profile macros.xls]Pivotx'!R1C1",
TableName:="PivotTable14", _
DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable14").PivotFields("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.

 

[George Nicholson]

Try changing

With ActiveSheet.PivotTables("PivotTable14").PivotFields("Shipper
Name")

to

With Worksheets("pivotx").PivotTables("PivotTable14").PivotFields("Shipper
Name")

since that was the destination of the PivotTable you added.

HTH,

 

[vrzimmerm]

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").AddDataField
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?

Try changing

With ActiveSheet.PivotTables("PivotTable14").PivotFields("Shipper
Name")

to

With Worksheets("pivotx").PivotTables("PivotTable14").PivotFields("Shipper
Name")

since that was the destination of the PivotTable you added.

HTH,

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:=xlDatabase,
SourceData:= _
"Details!R1C1:R8822C23").CreatePivotTable TableDestination:= _
"'[Import profile macros.xls]Pivotx'!R1C1",
TableName:="PivotTable14", _
DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable14").PivotFields("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.