Pivot Table Request

[Guest]

I tried to make a Pivot Table do the following:

"Show me the right value - copy the value to a Temporarily worksheet"

I tried the code shown below. How ever it keeps on giving me the same error
in:

ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="",
ColumnFields:="Activity", PageFields:="ARF Code"

Does someone see the error? Thanks in Advance!

Sub Get_DATA_ARF()
Dim cLastRow As Long
Dim i As Long
Dim iPos As Long
k = Sheets("LookUp").Range("AM1").Value

Windows("Kostenbeheerssysteem.xls").Activate
Sheets("Table Combi").Select
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.DataFields
pf.Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="",
ColumnFields:="Activity", PageFields:="ARF Code"
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Hours")
..Orientation = xlDataField
..Caption = "Sum of Hours"
..Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF Code").CurrentPage =
"" & k & ""
Next pf

With ActiveSheet.PivotTables("PivotTable3")
.ColumnGrand = False
.RowGrand = False
End With

Sheets.Add.Name = "Temp"

With ActiveSheet.PivotTables("PivotTable3")
Sheets("Temp").Range("A1").Value = pf.Value
End With
End Sub

 

[Debra Dalgleish]

Do you have two pivot tables on the Table Combi sheet? If not, replace
all the references to ActiveSheet.PivotTables("PivotTable3") with pt

In the line where you get the error, remove: RowFields:="",

In the last few lines:

With ActiveSheet.PivotTables("PivotTable3")
Sheets("Temp").Range("A1").Value = pf.Value
End With

pf would be Nothing, so you won't get a value on the Temp sheet.