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.
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Tom Farrar

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Michael Dobbins

ASKER
Thank you both for your help!
Michael Dobbins

ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Subodh Tiwari (Neeraj)

You're welcome Michael! Glad it helped to resolve the issue.