Macro to select dates in the page field of a pivot table

[teilenvk]

The below macro is suppose to create a sheet, add a pivot table and
then a data table that looks up pivot table data. Everything works
great except that the pivot table needs to be limited to data with a
specific date based on a cell entered by the user. The relavent lines
contain the variable B. I have tried defining B as a date and setting
the pivotfield = to the input cell directly. I have succesfully used
similar code with numbers such as 19. Is there something different
about dates?

Thanks!

Sub LFMStates()

X = "States"
Y = "LFM States breakout"
Z = "LFMStates"
A = "States!R3C10"
B = Worksheets("Input").Range("G7").Value
Application.ScreenUpdating = False
On Error Resume Next
Set wSheet = Worksheets(X)
If wSheet Is Nothing Then
Sheets.Add.Name = X
Sheets(X).Select

ActiveWorkbook.Worksheets("Graphs").PivotTables("PivotTable1").PivotCache.
_
CreatePivotTable TableDestination:=A, TableName:=X
With ActiveSheet.PivotTables(X)
.PivotFields("Bureau State").Orientation = xlRowField
.PivotFields("Year").Orientation = xlColumnField
.PivotFields("Valued As Of Date").Orientation = xlPageField
.AddDataField
ActiveSheet.PivotTables(X).PivotFields("Incurred Limited"), "Sum of
Incurred Limited", xlSum
End With
ActiveSheet.PivotTables(X).PivotFields("Valued As Of
Date").CurrentPage = B
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
ActiveSheet.PivotTables(X).ManualUpdate = True
'Gets state list from defintion tab, sets years, and fills table with
formulas that lookup the pivot table values
Range("A1").Select
Sheets("Definitions").Range("AB1:AC52").Copy
Destination:=ActiveCell
Range("C1").Formula = "=YEAR(EffDate)"
Range("D1").FormulaR1C1 = "=RC[-1]-1"
Range("D1").Copy Destination:=Range("D1:H1")
Range("C2").Formula = "=IF(ISERROR(GETPIVOTDATA(""Incurred
Limited"",$J$1,""Year"",C$1,""Bureau
State"",$A2)),0,GETPIVOTDATA(""Incurred
Limited"",$J$1,""Year"",C$1,""Bureau State"",$A2))"
Range("C2").Copy Destination:=Range("C2:H52")
Range("C2:H52").NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""??_);_(@_)"
Cells.EntireColumn.AutoFit
Else
If MsgBox(Y & " already completed. Keep existing analysis?",
vbYesNo) = vbNo Then
Application.DisplayAlerts = False
Sheets(X).Delete
Application.DisplayAlerts = True
Run (Z)
Else
MsgBox "You Cancelled"
End If
End If
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

since your cache is already populated with data, in the resulting pivot
table, you can use a pagefield with your date column and set the value to
the date provided by the user.

 

[teilenvk]

Isn't that what I'm doing on the line:

..PivotFields("Valued As Of Date").Orientation = xlPageField

When I step into the macro the page field is created, when I try to use
these lines to select a date, but nothing happens:

B = Worksheets("Input").Range("G7").Value
ActiveSheet.PivotTables(X).PivotFields("Valued As Of Date").CurrentPage
= B

I have checked that my date in Worksheets("Input").Range("G7") is date,
not text that looks like a date. I also tried make the date in that
cell a text date.

Any other ideas?