Centurion79b
New Member
- Joined
- Sep 21, 2015
- Messages
- 11
I have several pivot tables that only contain text comments by product and location. The problem I have is that when I refresh the pivot table the pivot items filter gets set to (blank) only which results in nothing being shown in the table. I tried writing code that would (select all) and then uncheck (blank) but there doesn't seem to be a way to (select all). So I have written some code to deselect (blanks) and select everything else. However this code runs really slow. It take more than 5 minutes to run through all the pivot tables. Here is my code:
There are 2 arrays in this code. Ary is an array of sheets that I want to process and RVary is an array of the pivot fields. Each sheet in Ary contain a pivot table called RVCS.
Any suggestions are greatly appreciated.
There are 2 arrays in this code. Ary is an array of sheets that I want to process and RVary is an array of the pivot fields. Each sheet in Ary contain a pivot table called RVCS.
Any suggestions are greatly appreciated.
Code:
Dim ary As Variant, RVary As Variant
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
ary = Array("CM PSL", "NM PSL", "CQ PSL", "NQ PSL", "CM Country", "NM Country", "CQ Country", "NQ Country", "QTR Summary", "TY PSL", "TY Country")
RVary = Array("CMRVCS", "NMRVCS", "CQRVCS", "NQRVCS")
Application.ScreenUpdating = False
On Error Resume Next
For Each i In ary
Sheets(i).Activate
For Each j In RVary
With ActiveSheet.PivotTables("RVCS")
With .PivotFields(j)
For k = 2 To .PivotItems.Count
.PivotItems(k).Visible = True
Next k
.PivotItems(1).Visible = False
End With
End With
Next j
Next i
Erase array1
Erase RVCS
Application.ScreenUpdating = True
Sheets("CM PSL").Select
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation