Pivot Table Request

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

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.


Jasper wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html