pivot table macro

  • Thread starter Thread starter skippy51
  • Start date Start date
S

skippy51

I want to create a pivot table using the pivot table wizzard from a worksheet
into a worksheet with the name table by running a macro. when I record the
macro I create the pivot table using the wizzard and it works fine but when I
try to run the macro again it errors "unable to get the pivot tables property
of the worksheet class" any help?
 
Sub pivottest1()
'
' pivottest1 Macro
' Macro recorded 5/11/2008 by Skip Bryczek
'

'
Sheets("temps").Select
Cells.Select
Selection.Clear
Range("A1").Select
Sheets("Temp_report").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Temp_report!R1C1:R697C16").CreatePivotTable TableDestination:= _
"'[Scanner Readings test1.xls]temps'!R1C1",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.PageFieldOrder = xlOverThenDown
.PageFieldWrapCount = 8
.RowGrand = False
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Asset", _
ColumnFields:="Item"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Reading")
.Orientation = xlDataField
.Caption = "Average of Reading"
.Function = xlAverage
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
 
Back
Top