我有一个电子表格,它有七个表(tbl_1,tbl_2 ...tbl_7),每个表都由自己的切片机控制。每个切割机有六个按钮(10,20,30,40,50,60)。我使用下面的代码在每个切片机上选择一个团队,然后为每个团队/切片器设置创建一个PDF。到目前为止,代码运行所需的时间从5-7分钟不等。任何帮助都是非常感谢的。
Sub SlicerTeam()
Dim wb As Workbook
Dim sc As SlicerCache
Dim si As SlicerItem
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
For x = 1 To 6
For i = 1 To 7
Set sc = wb.SlicerCaches("tbl_" & i)
sc.ClearAllFilters
For Each si In sc.VisibleSlicerItems
Set si = sc.SlicerItems(si.Name)
If Not si Is Nothing Then
If si.Name = x * 10 Then
si.Selected = True
Else
si.Selected = False
End If
Else
si.Selected = False
End If
Next si
Next i
Call PDFCreate
Next x
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
End Sub发布于 2017-02-17 00:13:40
假设这些切片器正在切割枢轴表,请尝试下面的代码。它可能有助于加快速度,这取决于您的PivotTables有多大。
Sub SlicerTeam()
Dim wb As Workbook
Dim sc As SlicerCache
Dim si As SlicerItem
dim pt as PivotTable
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
For Each pt in wb.PivotTables
pt.ManualUpdate = True
Next
For x = 1 To 6
For i = 1 To 7
Set sc = wb.SlicerCaches("tbl_" & i)
sc.ClearAllFilters
For Each si In sc.VisibleSlicerItems
Set si = sc.SlicerItems(si.Name)
If Not si Is Nothing Then
If si.Name = x * 10 Then
si.Selected = True
Else
si.Selected = False
End If
Else
si.Selected = False
End If
Next si
Next i
For Each pt in wb.PivotTables
pt.ManualUpdate = True
Next
Call PDFCreate
Next x
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
End Subhttps://stackoverflow.com/questions/42284468
复制相似问题