VBA delete if error only

[rajaa25]

Please help. This is not working as intended. I tried "Err" and "Error" but they do not work.

Thank you.





Sub Filter_Pivot_Actual()


'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("Var to PY").PivotTables("VartoPY")
Set Field = pt.PivotFields("EntityName")
NewCat = Worksheets("Var to PY").Range("c1").Value
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters


If (Field.CurrentPage = NewCat) = Error Then GoTo 1 Else GoTo 5

1: Field.CurrentPage = NewCat
2: Sheets("Var to PY").Delete
3: Exit Sub
4: GoTo 12





5: Field.CurrentPage = NewCat

6: Field.EnableItemSelection = False



7: Application.CellDragAndDrop = True


8: ActiveWorkbook.ShowPivotTableFieldList = False


9: ActiveFilters.Application.PivotTableSelection = False


10: Worksheets("Var to PY").Protect Password:="xxx"

11: End With

12: End Sub

 

[rajaa25]

There you go Joe. Sorry for the delayed response.

 

[Joe4]

OK, I think I finally see what you are talking about.
So, where do the values that make up the Pivot Filters in cell B1 come from?
What is the data source for that Pivot Filter?

 

[rajaa25]

OK, I think I finally see what you are talking about.
So, where do the values that make up the Pivot Filters in cell B1 come from?
What is the data source for that Pivot Filter?

The data source is a SQL query bringing in budgeted and actual amounts from the subledger and budgeting software.

 

[Joe4]

So, B1 is pulling directly from the SQL Query, and not from another part of your Workbook?
Or are the results from the SQL Query being returned to somewhere else in the Workbook, and B1 pulling from that?

I have to admit, I do not use Pivot Tables that much. So I was going to try to create your scenario of my side to see if I could get it to work, and then work my way through it. But I don't know if I am going to be able to create that sort of structure.

 

[rajaa25]

So, B1 is pulling directly from the SQL Query, and not from another part of your Workbook?
Or are the results from the SQL Query being returned to somewhere else in the Workbook, and B1 pulling from that?

I have to admit, I do not use Pivot Tables that much. So I was going to try to create your scenario of my side to see if I could get it to work, and then work my way through it. But I don't know if I am going to be able to create that sort of structure.

Yes, B1 is coming direct from a SQL query and the data does not reside in the workbook.

My VBA code should be going thru and deleting sheets where B1 does not equal to C1. What's the difference between err and error in VBA? I was trying to seperate the two scenarios but the Macro does not want to work for some reason.

 

[Joe4]

What's the difference between err and error in VBA? I was trying to seperate the two scenarios but the Macro does not want to work for some reason.

OK, let's go back your original question. I assume you are talking about this part of your code:

If (Field.CurrentPage = NewCat) = Error

That is not a valid statement. "Error" isn't a valid value here. The way you are using it, it would expect it to be a variable named "Error".

If you want to check to see if a statement returns an error, you can surround it in an IFERROR statement, as shown in the VBA section of this link here: MS Excel: How to use the ISERROR Function (WS, VBA)

However, in order for that to be true in your case, one of things you are checking would have to be returning an error.
If you simply want to check to see if they are not equal, you would use <>.

 

[rajaa25]

Joe, Is there a way to show a blank pivot table when C1 does not equal to anything on the pivot filter? This would also work and the sheet does not have to be deleted.

Thank you.

 

[Joe4]

I don't know. Typically, filters come right from the data itself and not from outside sources. I have never seen anyone do what you are trying to do.

Because that is a bit different from the original question, I recommend posting it as a new question. That way it will appear as a new, unanswered question and will get more looks, and hopefully someone who is more versed on pivot tables than me will see it and offer some advice.