Why does seemingly simple VBA make my workbook randomly crash?

[McMasher]

I've dug and dug and dug online to try and solve this before coming here, so I hope someone can help me.

I have really simple vba in this report to auto apply a couple of filters to the data by clicking a button. At some point, something happened that made it so when I opened the file, I could move around, scroll, cycle through report tabs, but as soon as I try to save the document (Ctrl+S or otherwise) or if I click Ctrl + F the excel workbook crashes.

I was able to solve this issue by simply deleting my vba out of the workbook...which tells me that my vba is the problem...but I have no idea what the problem could be. Consequently I can re-copy my vba code from notepad back into the excel workbook module and save, and everything works fine...

Excel 2013 64 bit

VBA:

Option Explicit


Sub CoreItemNeedFilter()
    
    On Error Resume Next
    ActiveSheet.Range("$A$12:$AI$30000").AutoFilter
    
    ActiveSheet.Range("$A$12:$AI$30000").AutoFilter Field:=5, Criteria1:=Array( _
        "Core Item Buffer", "PO Need with Buffer", "Purchase Order Need"), Operator:= _
        xlFilterValues


End Sub


Sub RegularItemNeedFilter()
    
    On Error Resume Next
    
    ActiveSheet.Range("$A$12:$AI$30000").AutoFilter
    
    ActiveSheet.Range("$A$12:$AI$30000").AutoFilter Field:=5, Criteria1:="Purchase Order Need", Operator:=xlFilterValues
    
End Sub


Sub ClearFilter()


    On Error Resume Next
    
    ActiveSheet.Range("$A$12:$AI$30000").AutoFilter
    
End Sub




Sub RefreshPivots()


Calculate
Do Until Application.CalculationState = xlDone
    DoEvents
Loop


Workbooks("Inventory Plan.xlsm").RefreshAll


End Sub

 

[Jeffrey Mahoney]

On first blush I'm wondering what the RefreshPivots is doing? The autofilter subs are not out of line. Get rid of this and see if the problem persists:

Do Until Application.CalculationState = xlDone
    DoEvents
Loop

Replace with Application.CalculateFull

 

[McMasher]

Thanks Jeff. I'll try switching that line of code and see what happens. If Application.CalculateFull is a more efficient way to keep the vba from running before the sheet is fully calculated then I have a lot of other, more complex macros that could use some cleaning so thank you for that little tidbit either way!

Also the RefreshPivots subroutine is refreshing all of the pivotcaches of the Inventory Plan sheet (a quick pivotcaches.count revealed that there are 4 pivot caches within the workbook itself). I have it calculate first just in case there are formulas in a data set that haven't been re-calculated yet...I realize that this could be just as easily done by hitting F9 and going to the Data tab and clicking refresh all...but those are precious half-seconds that no one has these days