Hello everybody!

I was wondering if someone could help me with the following. I wrote a macro
which get's information with matches the criteria k from a Pivot Table. I've
named K allready. Though it only returns "(Blank)" the criteria I stated in
case K was not found. I tested it with criterias which were sure to have
results but did not get any reslults other than (blank) either.

I think it has got something to do with the "On Error Resume Next" I used
but cant figure out what.

Application.CommandBars("PivotTable").Visible = False
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields ("ARF Code").CurrentPage =
"" & k & ""
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields ("ARF Code").CurrentPage =
"(blank)"
On Error GoTo 0
Application.CommandBars("PivotTable").Visible = False


Thanks in Advance!!

I assume you have assigned the name of the page to the variable K.
Your code is putting " around the variable name try removing those and just
using the K variable.
Also make sure that the K variable matches the format of the Page value in
the pivot table, i.e. 2 < 02.

Nick


"Jasper" wrote in message
...
Hello everybody!

I was wondering if someone could help me with the following. I wrote a
macro
which get's information with matches the criteria k from a Pivot Table.
I've
named K allready. Though it only returns "(Blank)" the criteria I stated
in
case K was not found. I tested it with criterias which were sure to have
results but did not get any reslults other than (blank) either.

I think it has got something to do with the "On Error Resume Next" I used
but cant figure out what.

Application.CommandBars("PivotTable").Visible = False
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields ("ARF Code").CurrentPage
=
"" & k & ""
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields ("ARF Code").CurrentPage
=
"(blank)"
On Error GoTo 0
Application.CommandBars("PivotTable").Visible = False


Thanks in Advance!!

One other point. If this is the exact code from your procedure then you will
always get <blank as that is the last statement.
So the code will show page K then <blank but unless you have a pause in
between you wont see it.

If you are trying to trap the error so that if the age K does not exist and
you want to show <blank then you need to build these statements in to a
error trapping routine or test the err.number in Select Case or an If
statement.

Nick


"Jasper" wrote in message
...
Hello everybody!

I was wondering if someone could help me with the following. I wrote a
macro
which get's information with matches the criteria k from a Pivot Table.
I've
named K allready. Though it only returns "(Blank)" the criteria I stated
in
case K was not found. I tested it with criterias which were sure to have
results but did not get any reslults other than (blank) either.

I think it has got something to do with the "On Error Resume Next" I used
but cant figure out what.

Application.CommandBars("PivotTable").Visible = False
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields ("ARF Code").CurrentPage
=
"" & k & ""
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields ("ARF Code").CurrentPage
=
"(blank)"
On Error GoTo 0
Application.CommandBars("PivotTable").Visible = False


Thanks in Advance!!

To bad, I tried both options but none fixed the bug. Could you tell me how to
use the "On error Resume Next" function? I think I made a mistake in that
line.

So if I want the Macro to fill in the Value stated under K

ActiveSheet.PivotTables("PivotTable3").PivotFields ("ARF Code").CurrentPage =
"" & k & ""

But if it does not find K It will fill in (Blank)

ActiveSheet.PivotTables("PivotTable3").PivotFields ("ARF Code").CurrentPage =
"(Blank)"

What macro function should I use then? Thanks in advance!

In order to throw an error you first need to set the CurrentPage to "(All)"
Then set the page you want using the variable K
If you don't set the page to all and the value of K is not found then the
current page will be renamed to the value in K.
So if you CurrentPage value is showing the items for say "A" and then you
set K to "Z" but "Z" does not exist in the pivot table the current page will
be renamed from "A" to "Z" and no error will be thrown.


example code could be


ActiveSheet.PivotTables("PivotTable3").PivotFields ("ARF Code").CurrentPage =
"(All)"
On Error Resume Next
ActiveSheet.PivotTables("PivotTable3").PivotFields ("ARF Code").CurrentPage =
k

If Err.Number=1004 then ' The error thrown when no value is available
ActiveSheet.PivotTables("PivotTable3").PivotFields ("ARF
Code").CurrentPage = "(All)"
End If

On Error Goto 0 ' Turn off error handler

Hope this helps.

Nick




"Jasper" wrote in message
...
To bad, I tried both options but none fixed the bug. Could you tell me how
to
use the "On error Resume Next" function? I think I made a mistake in that
line.

So if I want the Macro to fill in the Value stated under K

ActiveSheet.PivotTables("PivotTable3").PivotFields ("ARF Code").CurrentPage
=
"" & k & ""

But if it does not find K It will fill in (Blank)

ActiveSheet.PivotTables("PivotTable3").PivotFields ("ARF Code").CurrentPage
=
"(Blank)"

What macro function should I use then? Thanks in advance!