none
Having issues using my VBA code to set a Pivot Table date fitler after a set date. RRS feed

  • Question

  • Below is the erroneous code that I excerpted from my larger macro. I could have sworn this worked on friday but now I am at a loss. I am attempting to set the date filter for a date after the dateCheck variable. I can't for the life of me figure out what I wrong here. Can you see right off?

    Sub scratchboard()
    '
    ' Macro2 Macro
    '
    Dim dateCheck As Date
    dateCheck = Evaluate("TODAY() - 4")
    '
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add2 _
            Type:=xlAfter, Value1:=Format(dateCheck, "mm/dd/yyyy")
    End Sub
    Monday, August 1, 2016 7:18 PM

Answers

  • Hi,

     

    What is the error message? Which specific version of Office are you using? 

    If the error message is "Run-time error '1004': This isn't a valid date".

    Please open the worksheet, select any item of Date field in the pivot table, check the format of date in the formula bar. Then ensure the format of date in Format(dateCheck, "yyyy-mm-dd") is the same as shown in the formula bar.

    If the error message is "Run-time error'1004': Application-defined or object-defined error".

    Add the following code to clear filter in front of  PivotFilters.Add2 menthod

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters

    Thursday, August 4, 2016 2:12 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, August 2, 2016 1:33 AM
  • Hi,

     

    What is the error message? Which specific version of Office are you using? 

    If the error message is "Run-time error '1004': This isn't a valid date".

    Please open the worksheet, select any item of Date field in the pivot table, check the format of date in the formula bar. Then ensure the format of date in Format(dateCheck, "yyyy-mm-dd") is the same as shown in the formula bar.

    If the error message is "Run-time error'1004': Application-defined or object-defined error".

    Add the following code to clear filter in front of  PivotFilters.Add2 menthod

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters

    Thursday, August 4, 2016 2:12 AM
    Moderator
  • ... so. I figured out what the issue was.

    What I submitted always worked... the problem is that in this code dateCheck is the variable and in the main code the variable is dateValue.

    3 days it took me to realize this. Thank you all for your time.

    Thursday, August 4, 2016 5:53 PM
  • I did try this and in all truth I should have been doing this from the beginning in case someone messed with filters. In the end, it was my own stupidity.

    Thank you for the sugguestion though.

    Thursday, August 4, 2016 5:56 PM