Using VBA to update pivot table filter from multiple values in a list

I am looking to create a VBA macro that has the ability to update one pivot table filter based on multiple cell values. I have successfully been able to create VBA code to update the pivot table filter with ONE value but would like to modify this code to update the filter with MULTIPLE values.

Below is the code where I have successfully been able to update with ONE value:

Please Login or Register  to view this content.

My range of values that I would like to pull from is on Worksheet("INVOICE"), Range(S1:S15). Although this range goes down 15 rows, not all rows may have a value (case by case basis depending on invoice).

I am pretty new to writing VBA code but am looking for any help to find a solution.

Thank you,
Tyler

Two things:

1. I am surprised that a moderator hasn't jumped in and told you to use code tags. Normally give first timers a "pass" and do it for them. Since I am not a moderator, I can't do it for you.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

2. Attach a sample file
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you dflak for this information. I have followed the instructions for Rule 3 and have inserted the code tags.

For some reason, I am receiving error message "upload of file failed" when attempting to attach the .xlsm doc through manage attachements..

In theory, you should be stripping down your data to just enough to prove the point.

I suggest two things. Resave the file as XLSB. XLSB files are smaller by about 40%. So that still might not get there.

Second best is to compress the file and attach the ZIP file.

Key factor in PivotTable filtering is how your PivotTable is constructed.

1. From regular Excel Table/Range
This one requires loop through entire item list within field and .Visible property should be set for each one.
As well, each status change will cause pivot table to refresh. To avoid slowdown of process, you should set .ManualUpdate to true at start of loop, and
set it to false at end.

Sample code:

Please Login or Register  to view this content.

2. OLAP data model based Pivot Table.
I'd strongly recommend this approach. Since you have Office 365. Use PowerQuery to load data to data model and create pivot table from it.
You can then use SlicerCache's .VisibleItemsList and use array to select multiple items at once. See link for where I did this in another forum.

https://chandoo.org/forum/threads/ch...4/#post-224163