need help with macro

[lprabhu1107]

Hi

I have a macro to refresh pivottble and change date in page field. The macro is below

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 19-06-2011 by prabhu
'
'
Sheets("FJY Pivot").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("Snapshot").CurrentPage = _
Format(Date, "dd-mmm")
Sheets("Total Pivot").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotFields("Snapshot").CurrentPage = _
Format(Date, "dd-mmm")
Sheets("Trend MKT Pivot").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
Sheets("Trend TTL Pivot").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
End Sub

When i run this macro i get the following error code "unable to set the default property of the pivot item class"

Can any one help me to sort out the above code

Prabhu

 

[lprabhu1107]

any ideas to resolve this pls...

 

[lprabhu1107]

bump

 

[Marcelo Branco]

Hi,

Maybe this

Dim MyDate as Date
 
MyDate = Format(Date, "dd-mmm")

and instead of

[COLOR=#ff0000]ActiveSheet.PivotTables("PivotTable1").PivotFields("Snapshot").CurrentPage = _[/COLOR]
[COLOR=#ff0000]Format(Date, "dd-mmm")[/COLOR]

Try

[COLOR=black]ActiveSheet.PivotTables("PivotTable1").PivotFields("Snapshot").CurrentPage = _[/COLOR]
[COLOR=black]MyDate[/COLOR]

And check if there is a value in your field Snapshot = Date (today)

HTH

M.

 

[lprabhu1107]

Hi Marcelo,

Thanks for your inputs, but still iam getting the same error with your revised codes...

Prabhu