troubleshooting Question

Updating pivot field based on cell value

Avatar of Michael Dobbins
Michael Dobbins asked on
VBAMicrosoft ExcelMicrosoft Office
5 Comments1 Solution110 ViewsLast Modified:
I am trying to update a pivot field based on a cell value and having a button run the macro. Here is what I have so far:

Sub Updatedate()

Application.ScreenUpdating = False


Dim pt As PivotTable
Dim field As PivotField
Dim NewCat As String

Set pt = Worksheets("Daily Call Stats").PivotTables("PivotTable1")
Set field = pt.PivotFields("Start Date")
NewCat = Worksheets("Daily Call Stats").Range("B1").Value

With pt
field.ClearAllFilters
field.CurrentPage = NewCat
pt.RefreshTable
End With

Application.ScreenUpdating = True
MsgBox ("The call volume report has been updated")

End Sub

I am getting an error message on this line:

field.CurrentPage = NewCat

This worked in the past when it was triggered by a change in the cell. However, anytime I touched the cell the sheet would freeze. So, I am trying to move it to change at the click of a button. Once I made the change over it stopped working.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros