Having trouble executing a macro to do pivot tables.

[Pritesh Patel]

Hello,
I have just started doing pivot tables, and find them most useful in my dya
to day activities here, so I decided to automate doing them by recording a
new macro to do what I do manually.

I get to the point where I am populating the macro with the fields that I
need, and I get the following pop-up message:

Run-time error '1004':

Unable to get PivotFields property from PivotTables class

I then have the option to either end the macro or de-bug it.

If I choose to de-bug, The following line is highlighted in yellow
(suggesting that this is where the problem is):

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")

The full macor, which I use to calculate z-charts (for Statistical process
Control [SPC]) is:

Cells.Select
Range("A4").Activate
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!C1:C51").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Validity")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Validity")
.Orientation = xlDataField
.Position = 1
End With
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").Subtotals =
Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
Range("D10").Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
End With
Range("F4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "p-valid"
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(RC[-3]/(RC[-2]=RC[-3])),"" "",RC[-3]/(RC[-3]+RC[-2]))"
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(RC[-3]/(RC[-2]+RC[-3])),"" "",RC[-3]/(RC[-3]+RC[-2]))"
Range("F5").Select
Selection.AutoFill Destination:=Range("F5:F2369"), Type:=xlFillDefault
Range("F5:F2369").Select
ActiveWindow.LargeScroll Down:=-73
Range("F3").Select
ActiveCell.FormulaR1C1 = "P-bar ="
Range("G3").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(C[-1])"
Range("G3").Select
ActiveWorkbook.Names.Add Name:="pbar", RefersToR1C1:="=Sheet4!R3C7"
Range("G4").Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "z"
Range("G5").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR((RC[-1]-pbar)/SQRT(pbar*(1-pbar)/(RC[-4]+RC[-3]))),""
"",(RC[-1]-pbar)/SQRT(pbar*(1-pbar)/(RC[-4]+RC[-3])))"
Range("G5").Select
Selection.AutoFill Destination:=Range("G5:G2778"), Type:=xlFillDefault
Range("G5:G2778").Select
ActiveWindow.ScrollRow = 59
ActiveWindow.SmallScroll Down:=-66
Range("H4").Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "Above UCL of +3 ?"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="" "",""
"",IF(RC[-1]>3,""YES"",""NO""))"
Range("H5").Select
Selection.AutoFill Destination:=Range("H5:H2073"), Type:=xlFillDefault
Range("H5:H2073").Select
ActiveWindow.ScrollRow = 1
End Sub