Filtering a Pivot Table

[Starbucks Junkie]

Guys,



I have written some code, an extract below, whereby I need to Pivot data in a Pivot Table to only show certian date ranges, my problem is that where there are more dates than are defined in my Code it does not filter, in other words it will show all available dates and not just filter and show the dates I have selected.



How can I get around this?

Please Login or Register  to view this content.

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

EDITED THIS TIME. READ THE FORUM RULES BEFORE POSTING AGAIN

[Starbucks Junkie]

Apologies, and duly noted.

[DonkeyOte]

Untested...

Please Login or Register  to view this content.

[Starbucks Junkie]

Untested...

Please Login or Register  to view this content.

I get an error function on the bold command line above which reads;

Unable to get Floor Property of the WorksheetFunction Class

[DonkeyOte]

No need to quote prior posts in your reply - clutters the board and your thread.

I can't replicate that error unless flooring to 0 rather than 8
(that said I suspect you might have issues anyway regards date interpretations)

Can you post a sample - doesn't need to be your main file just something that mimics the setup you have.

[Starbucks Junkie]

Workbook attached as requested.

[DonkeyOte]

In terms of that specific section the below (run separately for sake of testing) works for me:

Please Login or Register  to view this content.

Let me know if you are still unable to run the above sub routine in your sample file (test stand alone first).

edit: I've not reviewed the remainder of the code in the parent routine of your sample file

[Starbucks Junkie]

I tried the stand alone code, it removed 12 of the dates and then it fell over with the same error message as before.

[DonkeyOte]

I don't have a 2003 install against which to test but here would be an alternative which avoids use of Floor altogether:

Please Login or Register  to view this content.

this also works without incident for me

[Starbucks Junkie]

This code when tested stand alone removes all fields bar the first one in the Pivot table.

[DonkeyOte]

Well all I can do at this juncture is pass on the version I used.

When sub routine Example is invoked the 9th - 16th Aug alone are visible in your PT.
(I am presuming you're running on a UK configuration given your locale)

[Starbucks Junkie]

It must be one of those MS 2007 / 2003 differences, as when I open your example and run the code I get exactly the same effect as I desribed, it hides all dates apart from the first one on the table!!

[Starbucks Junkie]

I think I have come up with a solution that works for me, its messy but it appears to work.

I count the number of records of dates in the PT, then create a For Next loop to hide each field in turn leaving the last field in the PT visible as this will always be the current date minus 1 day, then I make visible my required dates.

As I said messy but it works for me...

Thanks for your help anyway.

Please Login or Register  to view this content.

[DonkeyOte]

I just booted up an old machine/install and yes you're right the dates are interpreted differently between the versions
(and Floor won't handle a negative number pre XL2010)

Please Login or Register  to view this content.

[Starbucks Junkie]

I guess another quirk between XL versions.

I have used your code as it works and looks better than mine.

Thank you for all your help.

[romperstomper]

Following a PM discussion with DO as to why the code using CDate doesn't work in 2007 even though it does in 2003 (and should, IMO, in 2007), I did a little digging and it appears that you have to explicitly set the numberformat to a UK one otherwise 2007 reports the Value, Name and Caption properties of the pivotitems in US format. So:

Please Login or Register  to view this content.

should, I think, work in both.

FWIW.