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
--
Sharon

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.

"Sharon" wrote:

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
--
Sharon

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

"Martin" wrote:

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.

"Sharon" wrote:

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
--
Sharon

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

"olssonj" wrote:

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

"Martin" wrote:

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.

"Sharon" wrote:

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
--
Sharon

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).CurrentPa ge
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

--
Regards,
Tom Ogilvy


"Sharon" wrote:

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
--
Sharon

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
--
Sharon


"Tom Ogilvy" wrote:

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).CurrentPa ge
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

--
Regards,
Tom Ogilvy


"Sharon" wrote:

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
--
Sharon

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.

--
Regards,
Tom Ogilvy


"Sharon" wrote:

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
--
Sharon


"Tom Ogilvy" wrote:

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).CurrentPa ge
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

--
Regards,
Tom Ogilvy


"Sharon" wrote:

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
--
Sharon

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.
--
Sharon


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Sharon" wrote:

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
--
Sharon


"Tom Ogilvy" wrote:

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).CurrentPa ge
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

--
Regards,
Tom Ogilvy


"Sharon" wrote:

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
--
Sharon