0

I've been looking for an answer before, many topics similar but not the same are existing, and I couldn't find an answer to my issues.

I have a macro allowing me to change the filter of a PVT automatically, and refreshing the PVT. The filter is changed accordingly to the value of a cell (copy / paste of the value of the cell in the PVT filter)

Lately, every since I add some more data into the table related to the Pivot Table, I cannot run the macro anymore otherwise

"Microsoft Excel has stopped working".

The VBA code is still the same, nothing elsa hsa changed so I don't really understand.

Please find after the code:

Sub update_Filter()

    Application.ScreenUpdating = False

    ActiveSheet.Calculate

    'Set the Variables to be used

    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewFilter As String

    'Here you amend to suit your data

    Set pt = Worksheets("PVT Competition Data").PivotTables("PivotTable3")
    Set Field = pt.PivotFields("PN ******")
    NewFilter = Worksheets("PVT Competition Data").Range("B4").Value

    On Error Resume Next

    'This updates and refreshes the PIVOT table

    With pt
        Field.ClearAllFilters
        Field.CurrentPage = NewFilter
        pt.RefreshTable

        Range("E12:I12").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Range("E12").Select
    End With

End Sub

If you find something wrong to this or if you have any explanations, you will save my day (and for sure the followings...).

Also please ask if you need more details.

Thanks in advance!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
math
  • 1
  • 1
  • 3
    Don't use `On Error Resume Next` without proper error handling. This line hides all error messages but the errors still occur, you just cannot see them. Remove that line and if errors occur fix them properly instead of hiding the message. • You might also want to [avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to make your code faster and more reliable. Using `Select` and `Selection` is a bad practice. – Pᴇʜ Jul 20 '18 at 09:45
  • Thank you for your answer. I took off the On Error Resume Next, and there is no mistakes on my code, so this is not the issue.. – math Jul 20 '18 at 10:02
  • Did you also get rid of all those `.Select` and `Selection.` as explained in the link? Also make sure to update your Excel to the latest version build. *"Microsoft Excel has stopped working"* is no regular VBA error it means your Excel crashed because of a bug or something. So an update might fix that bug. • If that doesn't help debug your code and go through it line-by-line using F8 to see on which line Excel crashes. – Pᴇʜ Jul 20 '18 at 10:16
  • 2
    I actually found the issue... In the table with the data, from which the PVT is built, a column "Source" is existing. In this column, there is the possibility to add where an info is coming from. And when I removed all the website address from this column (with an http://...) the Excel file stopped to stop when running my macro. Thank you for you help, there was nothing to do with my code, but I wil lalso work around the Select and Selection in my code. – math Jul 20 '18 at 10:43

0 Answers0