VBA code for pivot-filter

[cii04mh5]

Hi, i'm having a problem with the VBA code for changing the report-filter in a pivot table.
Today i'm manually typing in the date in the vba code, as seen below (the date is in the format YYYYMMDD, in this case 20111001):

ActiveSheet.PivotTables("Pivottabell1").PivotFields( _
"[Invoice Date].[Calendar].[Date]").VisibleItemsList = Array("", _
"[Invoice Date].[Calendar].[Date].&[20111001]")

Instead of manually typing in the date in the code i want enter a value in the form YYYYMMDD and save it in to a Long variable and then be able to use this variable instead of manually typing in the date. Below is the code that i tried to use where "Day" is the variable containing the date.


Dim Day As Long
Day = InputBox("Insert date in format: YYYYMMDD")

ActiveSheet.PivotTables("Pivottabell1").PivotFields( _
"[Invoice Date].[Calendar].[Date]").VisibleItemsList = Array("", _
"[Invoice Date].[Calendar].[Date].&[Day]")

Unfortunately this doesn't work. I am going to use the value "Day" to update a number of pivot tables and that's why i can't type it in manually each time. Thankful for all your help!

/Martin

[mancubus]

hi cii04mh5.
wellcome to VBAX.

try this...


Sub PvtTest()

Dim pvtDate As String, pvtCrt As String
Dim Day As Date

pvtDate = Application.InputBox("Insert date")

If IsDate(pvtDate) Then
Day = DateValue(pvtDate)
Else
MsgBox "Please enter a valid date"
Exit Sub
End If

pvtCrt = Format(Day, "yyyymmdd")

'your code here

End Sub

[Aflatoon]

Your last line would need to be:
ActiveSheet.PivotTables("Pivottabell1").PivotFields( _
"[Invoice Date].[Calendar].[Date]").VisibleItemsList = Array("", _
"[Invoice Date].[Calendar].[Date].&[" & Day & "]")

so that you concatenate the Day variable into the field string.

[cii04mh5]

Thanks a lot! Now it works fine.