cii04mh5
10-24-2011, 12:56 AM
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
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