Pivot Table Request

J

Jasper

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top