Pivot table will not returned value's

J

Jasper

Goodmorning Everybody,

Yesterday i made a macro which gets the value from Cell AM1 and fits it as a
criteria in a Pivotfield in another file.

The macro as seen at the end, works smooth except for this part:

Set pt = Sheets("Table Combi").PivotTables("PivotTable3")
Set pf = pt.PivotFields("ARF Code")
ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF Code").CurrentPage =
"" & k & ""
If pf.CurrentPage <> " & k & " Then
pf.CurrentPage = "(Blank)"
End If

Does anyone know what I am doing wrong here? The problem is, that when the
value, AM1, is not found in the pivotpage it will give an error. THANKS in
ADVANCE!!

The Full macro:


Sub GET_ARF_DATA2()
Windows("Pre Planning.xls").Activate
Dim cLastRow As Long
Dim i As Long
Dim iPos As Long
k = Sheets("LookUp").Range("AM1").Value

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

For Each pf In pt.DataFields
pf.Orientation = xlHidden
pt.AddFields PageFields:="ARF Code"
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Cost")
..Orientation = xlDataField
..Caption = "Sum of Cost"
..Function = xlSum
..NumberFormat = "0.00"
End With

Set pt = Sheets("Table Combi").PivotTables("PivotTable3")
Set pf = pt.PivotFields("ARF Code")
ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF Code").CurrentPage =
"" & k & ""
If pf.CurrentPage <> " & k & " Then
pf.CurrentPage = "(Blank)"
End If

Application.CommandBars("PivotTable").Visible = False
Next pf
With pt
..ColumnGrand = False
..RowGrand = False
End With

Sheets("Table Combi").Select
Range("B5").Copy
Windows("Pre Planning.xls").Activate
Sheets("LookUp").Range("BS1").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.DisplayAlerts = False
Windows("Kostenbeheerssysteem.xls").Activate
Sheets("TEMP").Delete
Sheets("Index").Select
Windows("Pre Planning.xls").Activate
Application.DisplayAlerts = True
End Sub
 

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

Similar Threads

Pivot Table Request 1
Change report firlds in pivot tables and charts with one selection 0
Creating Pivot Table with Macro using Multiple Sheets 0
VBA Filtered Pivot Table Showing "Multiple Values Selected"; Only One Value Selected 0
VBA Pivot Table Error 1
Set Pivot Table Pages 1
Creating a dyanmic pivot table 2
Several Pivot Tables: Change one filter and adapt it for the other 2

Top