数据透视切片机更新缓慢，可以暂停所有功能，直到切片机更新完成吗？ VBA Excel [英] Pivot Slicer Update To Slow, Can I pause all functions until slicer update is complete? VBA Excel

问题描述

我的电子表格中有一个按钮点击项目，每个位置都设置一个切片器和适当的主管。问题是需要太长时间。在切片器上每次更改后，如将Smith设置为true，整个工作簿将重新计算所有公式，然后再转到下一个项目。我可以暂停我的工作簿中的所有公式，直到最后一个项目被设置，然后重新启用它们。

I have a button click item on my spreadsheet that sets a slicer with the appropriate supervisors per location. The problem is it takes too long. After every change on the slicer like setting "Smith" to true the entire workbook recalculates all the formulas before moving to the next item. Can I pause all of the formulas in my workbook until the last item is set and then re enable them.

Sub Rectangle_Click()
 ActiveWorkbook.SlicerCaches("Slicer_Supervisor1").ClearManualFilter
    With ActiveWorkbook.SlicerCaches("Slicer_Supervisor1")
        .SlicerItems("Smith").Selected = True
        .SlicerItems("Fox").Selected = True
        .SlicerItems("Dougan").Selected = True
        .SlicerItems("Milton").Selected = True
        .SlicerItems("Johnson").Selected = True
        .SlicerItems("Johnsen").Selected = True
        .SlicerItems("Jones").Selected = False
        .SlicerItems("Davis").Selected = False
        .SlicerItems("Miller").Selected = True
        .SlicerItems("Moore").Selected = False
        .SlicerItems("(blank)").Selected = True
        .SlicerItems("Taylor").Selected = True
        .SlicerItems("Anderson").Selected = True
    End With
    Call Auto_SortAndFilter
End Sub

推荐答案

我没有使用.SlicerItems方法，所以我不知道它可能会如何影响，但是，我想你只想把你的代码放在这两个命令。

I've not used the .SlicerItems method so I don't know how it might affect that, however, I think you just want to put your code within these two commands.

Application.Calculation = xlmanual 
Application.Calculation = xlautomatic

N

这篇关于数据透视切片机更新缓慢，可以暂停所有功能，直到切片机更新完成吗？ VBA Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！