Hi,

I have a drop down list that a user can select a period from (Q1,Q2,Q3,Q4). This list drives a named field called "Period". My code fires when the user makes a selection and changes the period on a string of pivots to reflect the selected period.

It works fine as long as the pivots have data/a filter item for the period selected. If it doesn't then excel presents the message "No item of this name exists within Pivot Table report. Rename Q2 to Q4?"

I've not done any error handling yet, so i googled how to use On Error and put something in the code but it doesn't work. Any idea on how i can get around this message and exit the sub with a message?

AB8 and V20 are the cell references of the pivot item list for each pivot table.

Thanks in advance,

J

Sub UpdateQuarteronPivots_Change()
Application.EnableEvents = False

Sheets("mysheet").Range("AB8") = Sheets("my sheet").Range("Period")
On Error GoTo LError

Sheets("mysheet").Range("V20") = Sheets("mysheet").Range("Period")
On Error GoTo IError

Application.EnableEvents = True

Exit Sub

LError: MsgBox "No Loyalty data available for this period"
Exit Sub

IError: MsgBox "No Invoice Data exists for this period. Please check source data before changing period"
Exit Sub

End Sub