Updating pivot field based on cell value

Michael Dobbins
Michael Dobbins used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Tom FarrarConsultant

Commented:
Not much into VBA, but does "Dim NewCat As String" need to be a date type?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
What's the format of the date cell B1? What's in there? Is it a date as a string or a real date?
Excel treats dates as numbers. In a blank cell, type the formula =ISNUMBER(B1), what does it return? If it returns TRUE, it means the date in B1 is the real date.

If the date in B1 is a real date, what if you replace the following line...

NewCat = Worksheets("Daily Call Stats").Range("B1").Value

WITH THIS...

NewCat = Format(Worksheets("Daily Call Stats").Range("B1").Value, "m/d/yyyy")

Author

Commented:
Thank you both for your help!

Author

Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial