Pivot Table Field - selecting yesterday's date

[Ughes]

Hello All,

I am having some difficulty with a slight spin on a familiar problem.

I am attempting to have my macro update the selected items in a pivot field. I want the macro to only select yesterdays date from the pivot field list.

At the moment, when i attempt to set the date as yesterdays, i get a "Type Mismatch" error from Excel.

Any ideas anyone, as I am stumped. Thanks sooo much.

Regards

Code below:


Sub Macro2()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("APAC").PivotTables(1)
Set pf = pt.PivotFields("Create Day")
Set pi = DateAdd("dd", -1, "mm/dd/yyyy")

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
With pf

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[Lidsavr]

Try the change highlighted in Orange and then use the line of code I added below to select the pivotfield.

Sub Macro2()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As [COLOR="DarkOrange"]Date[/COLOR]

Set pt = Sheets("APAC").PivotTables(1)
Set pf = pt.PivotFields("Create Day")
Set pi = DateAdd("dd", -1, "mm/dd/yyyy")

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
With pf

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Here is the line of code to use for the pivot field (change the PTName and PFName!)

    ActiveSheet.PivotTables("PTName").PivotFields("PFName").CurrentPage = pi

Charles

 

[Ughes]

Hi Charles

Thank you for reverting back to me so quickly and apologies for the delay in my response (Holidays).

I have updated the macro to look like this:

Sub Macro2()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As Date


Set pt = Sheets("APAC").PivotTables(1)
Set pf = pt.PivotFields("Create Day")
Set pi = DateAdd("dd", -1, "mm/dd/yyyy")

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
With pf

For Each pi In pf.PivotItems

ActiveSheet.PivotTables("PTName").PivotFields("PFName").CurrentPage = pi

'pi.Visible = Val(pi.Value) = CDate(Worksheets("APAC").Range("G3").Value)
Next pi

End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Unfortunately, when I run the macro I receive a Compile Error: "Object Required" at the line "Set pi = "

I was wondering if you know why i get this error? Something to do with how I have formatted the date, maybe?

Thank you so much for all of your assistance.

Regards
Ughes

 

[Ughes]

Hi Charles,

Was wondering if you had the opportunity to review the response I posted below to your original response?

Still looking to achieve what I set out originally and would greatly appreciate your assistance.

Kind regards
Ughes

 

[Andrew Poulsom]

Maybe:

Set pi = pf.PivotItems(Format(DateAdd("dd", -1, Date), "mm/dd/yyyy"))