Hello
Here is my module:
Sub ItemDetail2014Sales()
Dim ItemCodeVariable As Variant
Dim Response As String
ItemCodeVariable = Range("B" & ActiveCell.Row).Value
Response = MsgBox("Do you want to see the 2014 Item Detail? It may take up to 30 seconds.", vbOKCancel)
If Response <> vbOK Then
Exit Sub
End If
Sheets("Item Detail").PivotTables("ItemDetailPivotTable").PivotFields("Item Code").ClearAllFilters
Sheets("Item Detail").PivotTables("ItemDetailPivotTable").PivotFields("TxnDate").ClearAllFilters
Sheets("Item Detail").PivotTables("ItemDetailPivotTable").PivotFields( _
"Customer Name").ClearAllFilters
On Error GoTo ErrorHandler:
Sheets("Item Detail").PivotTables("ItemDetailPivotTable").PivotFields("TxnDate").PivotFilters. _
Add2 Type:=xlDateThisYear
Sheets("Item Detail").PivotTables("ItemDetailPivotTable").PivotFields("Item Code").CurrentPage = ItemCodeVariable
Sheets("Item Detail").Visible = True
Sheets("Item Detail").Select
Exit Sub
ErrorHandler:
MsgBox ("You may have accidentally clicked the Header or an Item Code with no Sales History. Try again.")
End Sub
The following code is necessary, because if the PivotFields are already set to the specified values, it errors:
Sheets("Item Detail").PivotTables("ItemDetailPivotTable").PivotFields("Item Code").ClearAllFilters
Sheets("Item Detail").PivotTables("ItemDetailPivotTable").PivotFields("TxnDate").ClearAllFilters
Sheets("Item Detail").PivotTables("ItemDetailPivotTable").PivotFields( _
"Customer Name").ClearAllFilters
However, it takes too long to process. Is there a way that I can do this differently, with IF statements or other language that will speed the process up? I was thinking perhaps an IF statement checking if the PivotField is already equal to xlDateThisYear or the current ItemCodeVariable...I don't know how to do it though.
Thanks for all help in advance.
Bookmarks