Link to home
Start Free TrialLog in
Avatar of Michael Dobbins
Michael Dobbins

asked on

Updating pivot field based on cell value

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.
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

Not much into VBA, but does "Dim NewCat As String" need to be a date type?
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Michael Dobbins
Michael Dobbins

ASKER

Thank you both for your help!
Thanks, Subodh Tiwari (Neeraj)! The format of my date cell compared to the format of the data was not the same. Adding your update but changing the format to match what was in my data("dd-mmm") worked perfectly.
You're welcome Michael! Glad it helped to resolve the issue.