(The number format will be stuck on General if there are blanks in your source data. I wrote about this here and apart from changing the number format of your field, you may also like to try Micheal's approach from the comments if you have Excel 2010.I see there's also a possible solution at this SO thread.

pvtdating com-82

For eg user enters start date as : and end date as . This is the only date(pivotitem) existing in the Pivot field it1 = "01/01/2013" it2 = "01/01/2014" Dim dt Temp1 As Date Dim dt Temp2 As Date Dim date Array() dt Temp1 = Format(CDate(it1), "dd/mm/yyyy") dt Temp2 = Format(CDate(it2), "dd/mm/yyyy") No Of Days = Date Diff("D", dt Temp1, dt Temp2) Re Dim date Array(No Of Days) For i = 0 To No Of Days date Array(i) = dt Temp1 + i Next Or can you suggest me a way where I can put the Dates entered by the user in some Array. so a table or a list contains all the Dates between these Dates and I can use .application.match(Arrayof Dates, pt Item. But I dont know how to make an Array of Dates given by the user Option Explicit Sub Macro7() Dim Start Date As Date Dim End Date As Date Dim pvt DATE As Date Dim pvt Field As Pivot Field Dim x As Pivot Item Dim it1 Dim it2 Dim y it1 = "2/2/2016" it2 = "2/20/2016" Set pvt Field = Excel. If you have Excel 2010 or later and these Pivot Tables share the same Pivot Cache (i.e.

Display Alerts = True Exit Sub End If End If Next i End With End Sub Hallo SIva, thanku for your reply. I have a userfrom that takes the starting date and ending date from the user and i made a macro in order to select all the Dates in the Pivot field " planned rel" between the Dates entered by the user. Pivot Fields(2) Start Date = Format(CDate(it1), "dd/mm/yyyy") End Date = Format(CDate(it2), "dd/mm/yyyy") For Each x In pvt Field. I want that my code should mark the pivotitem of the respective field in each worksheet and with this one it's not doing for a single one.

I really appreciate your efforts :) But I am having like 4 worksheets and in 3 of them the field is there.

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.

This is a function I am calling in my main just to give my field Name and the Dates I want to compare. they have the same data source) then you can simply set up a slicer, then connect it to the other Pivot Tables.

After the 2 if conditions I want it to mark (select/Tickmark) the pivotitems in the field and otherwise unmark/ not select the other ones. Then any changes you make in one will automatically be reflected in the others.(Right click on the Slicer and select Report Connections).

Make the other one's invisible and then just Exit Sub. Pivot Items(i) If (dt Temp = dt From) Then On Error Resume Next Application. In regards to why your code is failing, can you advise what version of Excel you are using, and what the number format of the Pivot Field is?

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): Active Sheet. Pivot Fields( _ "[Invoice Date].[Calendar].[Date]"). Visible Items List = 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. Sub Pvt Test() Dim pvt Date As String, pvt Crt As String Dim Day As Date pvt Date = Application.

Below is the code that i tried to use where "Day" is the variable containing the date. Input Box("Insert date") If Is Date(pvt Date) Then Day = Date Value(pvt Date) Else Msg Box "Please enter a valid date" Exit Sub End If pvt Crt = Format(Day, "yyyymmdd") 'your code here End Sub Your last line would need to be: Active Sheet. Pivot Fields( _ "[Invoice Date].[Calendar].[Date]"). Visible Items List = Array("", _ "[Invoice Date].[Calendar].[Date].&[" & Day & "]") so that you concatenate the Day variable into the field string.

Dim Day As Long Day = Input Box("Insert date in format: YYYYMMDD") Active Sheet. Pivot Fields( _ "[Invoice Date].[Calendar].[Date]"). Visible Items List = Array("", _ "[Invoice Date].[Calendar].[Date].&[Day]") Unfortunately this doesn't work.