PDA

View Full Version : Solved: Pivot Table Error



marshybid
04-25-2008, 08:10 AM
Hi All,

I wonder if any of you could shed some light on the following. I have the following macro that I want to run ona specific report 2-3 times per week.


Range("A1:AR1").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Selection.AutoFilter
Range("A1").Select
Columns("Y:Y").Select
Selection.Insert Shift:=xlToRight
Range("Y1").Select
ActiveCell.FormulaR1C1 = "Timesheet For Week Ending"
Range("Y2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+CHOOSE(WEEKDAY(RC[-1]),0,6,5,4,3,2,1)"
Range("Y2").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
Columns("AS:AS").Select
Selection.Insert Shift:=xlToRight
Range("AS1").Select
ActiveCell.FormulaR1C1 = "Approved in Week Ending"
Range("AS2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+CHOOSE(WEEKDAY(RC[-1]),0,6,5,4,3,2,1)"
Range("AS2").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
Columns("AT:AT").Select
Selection.Insert Shift:=xlToRight
Range("AT1").Select
ActiveCell.FormulaR1C1 = "Total Amount"
Range("AT2").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-14]*RC[-31])+(RC[-13]*RC[-30])+(RC[-12]*RC[-29])"
Columns("AT:AT").Select
Selection.NumberFormat = _
"_-[$?-809]* #,##0.00_-;-[$?-809]* #,##0.00_-;_-[$?-809]* ""-""??_-;_-@_-"
Range("AT2").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
Range("A1").Select


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Approved Timesheets'!C1:C47").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ID")
.Caption = "Order ID"
.Subtotals = Array(False, False, False, False, False, False, False, False, False, _
False, False, False)
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Timesheet ID").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("X-Ref PO ID").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("User ID").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Contingent Staff First Name").Subtotals = Array(False, False, False, False, False, _
False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Contingent Staff Last Name" _
).Subtotals = Array(False, False, False, False, False, False, False, False, False, False _
, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Standard Rate").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Overtime Rate").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Second Overtime Rate"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Regular Hours").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Overtime Hours"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Total Second Overtime Hours").Subtotals = Array(False, False, False, False, False, _
False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Amount").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Order ID", _
"X-Ref PO ID", "User ID", "Contingent Staff First Name", _
"Contingent Staff Last Name", "Timesheet ID", "Regular Hours", "Standard Rate", _
"Total Overtime Hours", "Overtime Rate", "Total Second Overtime Hours", _
"Second Overtime Rate", "Total Amount")
ActiveSheet.PivotTables("PivotTable1").PivotFields("Timesheet ID").Orientation _
= xlDataField
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pivot Table"
Rows("4:4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D:D,E:E").Select
Range("E1").Activate
Selection.ColumnWidth = 9.71
Columns("F:F").ColumnWidth = 9.43
Range("H:H,J:J,L:L,M:M").Select
Range("M1").Activate
Selection.NumberFormat = _
"_-[$?-809]* #,##0.00_-;-[$?-809]* #,##0.00_-;_-[$?-809]* ""-""??_-;_-@_-"
Range("A1").Select
Columns("A:A").ColumnWidth = 10.57
Columns("B:B").ColumnWidth = 10.57
Columns("N:N").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select

End Sub


When I run this I get the following error message:

Run-time error '1004':

Unable to get the PivotFields property of the PivotTable class

Can anyone please help. I just copied this from recording a macro, the only thing I changed was the PivotField ('Regular Hours') but even when I run it with the original recorded macro it crashes at the same point.

Thanks,

It's Friday

tstav
04-25-2008, 10:22 AM
I don't feel I have the necessary experience on Pivots to engage on this issue, but I think that the following lines should come right after adding the PivotCache. I mean, shouldn't fields be added after the creation of the PivotCache? I found these lines deep down in your code.

ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Order ID", _
"X-Ref PO ID", "User ID", "Contingent Staff First Name", _
"Contingent Staff Last Name", "Timesheet ID", "Regular Hours", "Standard Rate", _
"Total Overtime Hours", "Overtime Rate", "Total Second Overtime Hours", _
"Second Overtime Rate", "Total Amount")
ActiveSheet.PivotTables("PivotTable1").PivotFields("Timesheet ID").Orientation _
= xlDataField
Are you sure this came out of the recorder as one piece, or did you do some cutting and pasting among the lines?
Regards