Mixing
how to speed up slicer vba code selection
up vote
0
down vote
favorite
hope everyone is well.
Ive got this code that makes a slicer selection (first item):
Sub test()
Dim sc As SlicerCache
Set sc = ActiveWorkbook.SlicerCaches("Slicer_book1")
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each pt In sc.PivotTables
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_book1")
.ClearManualFilter
cnt = .SlicerItems.Count
If cnt > 1 Then
For i = 2 To cnt
.SlicerItems(i).Selected = False
Next
End If
End With
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
somehow, this code works, but it is incedibly show. So, I was wondering whether anyone has any advice re how to properly speed this up?
Thanks and regards.
excel vba excel-vba
asked 2 days ago
RVH
328
add a comment |
up vote
0
down vote
favorite
hope everyone is well.
Ive got this code that makes a slicer selection (first item):
Sub test()
Dim sc As SlicerCache
Set sc = ActiveWorkbook.SlicerCaches("Slicer_book1")
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each pt In sc.PivotTables
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_book1")
.ClearManualFilter
cnt = .SlicerItems.Count
If cnt > 1 Then
For i = 2 To cnt
.SlicerItems(i).Selected = False
Next
End If
End With
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
somehow, this code works, but it is incedibly show. So, I was wondering whether anyone has any advice re how to properly speed this up?
Thanks and regards.
excel vba excel-vba
asked 2 days ago
RVH
328
This question might better fit to Code Review Stack Exchange.
– Pᴇʜ
2 days ago
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
hope everyone is well.
Ive got this code that makes a slicer selection (first item):
Sub test()
Dim sc As SlicerCache
Set sc = ActiveWorkbook.SlicerCaches("Slicer_book1")
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each pt In sc.PivotTables
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_book1")
.ClearManualFilter
cnt = .SlicerItems.Count
If cnt > 1 Then
For i = 2 To cnt
.SlicerItems(i).Selected = False
Next
End If
End With
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
somehow, this code works, but it is incedibly show. So, I was wondering whether anyone has any advice re how to properly speed this up?
Thanks and regards.
excel vba excel-vba
asked 2 days ago
RVH
328
hope everyone is well.
Ive got this code that makes a slicer selection (first item):
Sub test()
Dim sc As SlicerCache
Set sc = ActiveWorkbook.SlicerCaches("Slicer_book1")
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each pt In sc.PivotTables
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_book1")
.ClearManualFilter
cnt = .SlicerItems.Count
If cnt > 1 Then
For i = 2 To cnt
.SlicerItems(i).Selected = False
Next
End If
End With
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
somehow, this code works, but it is incedibly show. So, I was wondering whether anyone has any advice re how to properly speed this up?
Thanks and regards.
excel vba excel-vba
excel vba excel-vba
asked 2 days ago
RVH
328
asked 2 days ago
RVH
328
edited 2 days ago
asked 2 days ago
RVH
328
asked 2 days ago
RVH
328
asked 2 days ago
RVH
328
328
This question might better fit to Code Review Stack Exchange.
– Pᴇʜ
2 days ago
add a comment |
This question might better fit to Code Review Stack Exchange.
– Pᴇʜ
2 days ago
This question might better fit to Code Review Stack Exchange.
– Pᴇʜ
2 days ago
This question might better fit to Code Review Stack Exchange.
– Pᴇʜ
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
I prefer to use the pivot table name than slicer.
Sub test()
Dim sc As SlicerCache
Dim SIName As String
Dim pt As PivotTable, PTF As PivotField
Set sc = ActiveWorkbook.SlicerCaches("Slicer_book1")
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each pt In sc.PivotTables
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_book1")
'.ClearManualFilter
cnt = .SlicerItems.Count
If cnt > 1 Then
SIName = .SlicerItems(1).Name
End If
End With
'Pivot is the sheet name where the pivot table is located
ActiveWorkbook.Worksheets("Pivot").Activate
Set pt = ActiveWorkbook.Worksheets("Pivot").PivotTables("NameOfPivotTable")
'Book is pivot field
Set PTF = pt.PivotFields("Book")
PTF.ClearAllFilters
PTF.CurrentPage = SIName
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
End Sub
Hope this help
answered 2 days ago
adhy wijaya
42817
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
I prefer to use the pivot table name than slicer.
Sub test()
Dim sc As SlicerCache
Dim SIName As String
Dim pt As PivotTable, PTF As PivotField
Set sc = ActiveWorkbook.SlicerCaches("Slicer_book1")
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each pt In sc.PivotTables
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_book1")
'.ClearManualFilter
cnt = .SlicerItems.Count
If cnt > 1 Then
SIName = .SlicerItems(1).Name
End If
End With
'Pivot is the sheet name where the pivot table is located
ActiveWorkbook.Worksheets("Pivot").Activate
Set pt = ActiveWorkbook.Worksheets("Pivot").PivotTables("NameOfPivotTable")
'Book is pivot field
Set PTF = pt.PivotFields("Book")
PTF.ClearAllFilters
PTF.CurrentPage = SIName
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
End Sub
Hope this help
answered 2 days ago
adhy wijaya
42817
add a comment |
up vote
0
down vote
I prefer to use the pivot table name than slicer.
Sub test()
Dim sc As SlicerCache
Dim SIName As String
Dim pt As PivotTable, PTF As PivotField
Set sc = ActiveWorkbook.SlicerCaches("Slicer_book1")
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each pt In sc.PivotTables
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_book1")
'.ClearManualFilter
cnt = .SlicerItems.Count
If cnt > 1 Then
SIName = .SlicerItems(1).Name
End If
End With
'Pivot is the sheet name where the pivot table is located
ActiveWorkbook.Worksheets("Pivot").Activate
Set pt = ActiveWorkbook.Worksheets("Pivot").PivotTables("NameOfPivotTable")
'Book is pivot field
Set PTF = pt.PivotFields("Book")
PTF.ClearAllFilters
PTF.CurrentPage = SIName
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
End Sub
Hope this help
answered 2 days ago
adhy wijaya
42817
add a comment |
up vote
0
down vote
up vote
0
down vote
I prefer to use the pivot table name than slicer.
Sub test()
Dim sc As SlicerCache
Dim SIName As String
Dim pt As PivotTable, PTF As PivotField
Set sc = ActiveWorkbook.SlicerCaches("Slicer_book1")
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each pt In sc.PivotTables
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_book1")
'.ClearManualFilter
cnt = .SlicerItems.Count
If cnt > 1 Then
SIName = .SlicerItems(1).Name
End If
End With
'Pivot is the sheet name where the pivot table is located
ActiveWorkbook.Worksheets("Pivot").Activate
Set pt = ActiveWorkbook.Worksheets("Pivot").PivotTables("NameOfPivotTable")
'Book is pivot field
Set PTF = pt.PivotFields("Book")
PTF.ClearAllFilters
PTF.CurrentPage = SIName
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
End Sub
Hope this help
answered 2 days ago
adhy wijaya
42817
I prefer to use the pivot table name than slicer.
Sub test()
Dim sc As SlicerCache
Dim SIName As String
Dim pt As PivotTable, PTF As PivotField
Set sc = ActiveWorkbook.SlicerCaches("Slicer_book1")
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each pt In sc.PivotTables
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_book1")
'.ClearManualFilter
cnt = .SlicerItems.Count
If cnt > 1 Then
SIName = .SlicerItems(1).Name
End If
End With
'Pivot is the sheet name where the pivot table is located
ActiveWorkbook.Worksheets("Pivot").Activate
Set pt = ActiveWorkbook.Worksheets("Pivot").PivotTables("NameOfPivotTable")
'Book is pivot field
Set PTF = pt.PivotFields("Book")
PTF.ClearAllFilters
PTF.CurrentPage = SIName
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler
End Sub
Hope this help
answered 2 days ago
adhy wijaya
42817
answered 2 days ago
adhy wijaya
42817
answered 2 days ago
adhy wijaya
42817
answered 2 days ago
adhy wijaya
42817
42817
add a comment |
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373287%2fhow-to-speed-up-slicer-vba-code-selection%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
This question might better fit to Code Review Stack Exchange.
– Pᴇʜ
2 days ago