VBA - Pivot table multiple itens start with...
Good afternoon.
I would like to ask you about a way to select multiple itens on a pivot table, starting by "BK -"
I tried severals ways but nothing worked till now.
Below, you will find the code that I have for the moment.
If someone can help me, will be grateful.
Last code that I tied is :
Please Login or Register to view this content.
Unfortunatly it did not work.Please Login or Register to view this content.
Can you please help me ?
Waiting for your feedback
Regards
Last edited by ozstrik3r69; 06-16-2017 at 12:35 PM.
Since your "COMMENT_DESCRIPTION" field is in xlPageField.
You won't be able to use .PivotFilters.
You will need to iterate over all items in the field.
Also, since PivotField requires at least 1 item to be visible, to avoid issue, you should set all items to visible before you apply your filter.
Code should be something like...
Please Login or Register to view this content.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
@CK76
Thank you for your answer.
Actually I can see that it works.
I had to change name for the Dim and so on but actually it works.
The only "problem" I noted, is that it take too much time to create this filter.
I was wondering if it took that time because of the quantity of reasons available in the comment descritpion or because it is a long process ?
If you know a way to get that filter faster, I will be grateful.
Waiting for your feedback
Regards
It takes long time, since pivot updates itself every time visible status is set on each item.
I'd recommend you add following lines.
Before you start loop.
Then at end of the procedure.Please Login or Register to view this content.
EDIT:Please Login or Register to view this content.
Only other way to speed up the process is to use OLAP based PivotTable.
OLAP based Pivots has different methods than regular Pivots and does not need to loop through each item.
Last edited by CK76; 06-16-2017 at 02:17 PM. Reason: See Edit:
Wow !
I did not know about the OLAP extension.
It seems a bit complicated but interesting. I will take a look on it.
Regarding ManualUpdate I will try and will revert asap.
I was also thinking about another way.
If it take too long to use the filter "BK -" I have another possibility is to filter by date.
I have to find a VBA about a way to filter by week number.
If you know about it, I will be grateful.
Revert soon & waiting for your feedback
regards
Regardless of what field is used, unless the field is in Row Label field, code will be very similar to solution above. In that, it has to iterate through each item.
One way to get around this, is to add helper column to source range. To group items of interest into single value.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks