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("Pivot Table1")
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.
Sub Updatedate()
Application.ScreenUpdating
Dim pt As PivotTable
Dim field As PivotField
Dim NewCat As String
Set pt = Worksheets("Daily Call Stats").PivotTables("Pivot
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
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.
Not much into VBA, but does "Dim NewCat As String" need to be a date type?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for your help!
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.
You're welcome Michael! Glad it helped to resolve the issue.