Pivot page fields!

[Guest]

Hi - I have code that automatically selects an item from a series of pivot
page fields when the user clicks on a button. However, if the item does not
exist in the page field dropdown, the code crashes and I don't know to trap
that error and tell the code to move on to the next thing? Any help would be
very, very gratefully received!!

This is sort of a repost and I don't think I explained the problem very well
last time.

Thanks

 

[Guest]

There are a couple of approaches. One is to loop through the PivotFields
collection for your pivot table using a For Each loop (the code would be
something like:

Dim myField As PivotField
For Each myField In Worksheets("sheet3").PivotTables(1)
If myField.Name = "xxx" Then
....
....
Endif
Next

If you do this, if the name doesn't exist the code won't fall over, just
move on.

Alternatively make a note of the error code number and use error trapping:

Sub ...
On Error Goto handler

[your macro]

Exit Sub

handler:
If Err.Number = xxx Then
Resume Next
End If

End Sub

But I think the For loop's the best solution.

 

[Guest]

I think you stated the problem clearly - however, apparently you didn't
understand the answer. Her is some sample code that may provide a clue:

Sub MatchPages()
Dim s As String
s = ActiveSheet.PivotTables(1).PageFields(1).CurrentPage
Debug.Print s
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> ActiveSheet.Name Then
For Each pvt In sh.PivotTables
If s = "(All)" Then
pvt.PageFields(1).CurrentPage = "(All)"
Else
For Each pitm In pvt.PageFields(1).PivotItems
If pitm.Value = s Then
pvt.PageFields(1).CurrentPage = pitm.Value
Exit For
End If
Next
End If
Next
End If
Next

End Sub

 

[Guest]

Hi Tom and Martin

I didn't know there was an answer!! I am going to try both methods and see.
Thank you both for your help. Tom - you are a wonder!!

Sharon

 

[Guest]

Well, it appears that Martin understood you to say the Pivot Field itself
doesn't exist. I understood you to say an item in a pagefield does not
exist.

Also, Martin's code is pseudo code and will not run as written, so you would
need to clean it up.

 

[Guest]

Hi Tom you are right. It is when an item in a pagefield doesn't exist. I
am going over your code now to work in the variable that captures the
required item in the first place, i.e. when the user clicks on a button, the
item is captured in a variable. Thanks a lot. I know it is going to work
beautifully.

 

[Guest]

Hello guys. I have a little issue that may relate to Sharon's.

I have a code that establishes the item of a pagefield from a list of
several "Base" entries (for example, "A,B,C,D"). My problem is that not all
the "Base" entries are included in my pivottables.

Hence, whenever a "Base" entry is not found in a certain pivottable (i.e.,
establishing a value of "C" in a pivottable that includes only "A,B,D" as
possibles entries for its pagefield), Excel will actually overwrite one of
the entries of the pagefield with the datafield corresponding to another
entry (resulting in a pagefield with "A,C,D" with data for "A,B,C").

Is there any way to block or lock the available entries for a pagefield?

The VBA code I'm using is the following:

ActiveSheet.PivotTables("Table1").PageFields("Page1").CurrentPage = "C"

Thanks in advance.

 

[olssonj]

Hi Martin!

I have a small problem similar to this question. I want to make some items
visible and some not visible. I know all possible items but I dont know if
they exist as a pivotitem in a certain time. so... I thought that I could use
ure example with For Each but For Each PivotItem instead of For Each
PivotField... This is my code sample:

Dim itm As PivotItem

For Each itm In Worksheets("xxx").PivotTables("yyy"). _
PivotFields("zzz")

If itm.Name = "vvv" Then itm.Visible = True
If itm.Name = "uuu" Then itm.Visible = False
...
...
Next

The error says number 438.

Appreciate your help!!!
Thanks!
//Jonas

There are a couple of approaches. One is to loop through the PivotFields
collection for your pivot table using a For Each loop (the code would be
something like:

Dim myField As PivotField
For Each myField In Worksheets("sheet3").PivotTables(1)
If myField.Name = "xxx" Then
....
....
Endif
Next

If you do this, if the name doesn't exist the code won't fall over, just
move on.

Alternatively make a note of the error code number and use error trapping:

Sub ...
On Error Goto handler

[your macro]

Exit Sub

handler:
If Err.Number = xxx Then
Resume Next
End If

End Sub

But I think the For loop's the best solution.

Hi - I have code that automatically selects an item from a series of pivot
page fields when the user clicks on a button. However, if the item does not
exist in the page field dropdown, the code crashes and I don't know to trap
that error and tell the code to move on to the next thing? Any help would be
very, very gratefully received!!

This is sort of a repost and I don't think I explained the problem very well
last time.

Thanks

 

[olssonj]

Hi Again!

I must have been tired... Found the novice problem as soon I took a break
and returned to my nice program! Thanks...

//Jonas

Hi Martin!

I have a small problem similar to this question. I want to make some items
visible and some not visible. I know all possible items but I dont know if
they exist as a pivotitem in a certain time. so... I thought that I could use
ure example with For Each but For Each PivotItem instead of For Each
PivotField... This is my code sample:

Dim itm As PivotItem

For Each itm In Worksheets("xxx").PivotTables("yyy"). _
PivotFields("zzz")

If itm.Name = "vvv" Then itm.Visible = True
If itm.Name = "uuu" Then itm.Visible = False
...
...
Next

The error says number 438.

Appreciate your help!!!
Thanks!
//Jonas

There are a couple of approaches. One is to loop through the PivotFields
collection for your pivot table using a For Each loop (the code would be
something like:

Dim myField As PivotField
For Each myField In Worksheets("sheet3").PivotTables(1)
If myField.Name = "xxx" Then
....
....
Endif
Next

If you do this, if the name doesn't exist the code won't fall over, just
move on.

Alternatively make a note of the error code number and use error trapping:

Sub ...
On Error Goto handler

[your macro]

Exit Sub

handler:
If Err.Number = xxx Then
Resume Next
End If

End Sub

But I think the For loop's the best solution.

Hi - I have code that automatically selects an item from a series of pivot
page fields when the user clicks on a button. However, if the item does not
exist in the page field dropdown, the code crashes and I don't know to trap
that error and tell the code to move on to the next thing? Any help would be
very, very gratefully received!!

This is sort of a repost and I don't think I explained the problem very well
last time.

Thanks