Set Pivot Table Values to a List

I developed this piece of code to set pivot table values in a "template." The pivot table computes labor efficiencies and has filters for projects, commodities and work centers. I have these recorded in an Excel table and I loop through the table to read the values for each. Then I call this program.

For example, a Project list may contain Project 01,Project 02,Project03. Note that there are no spaces. The actual values are separated by commas. There are similar lists for commodities and work centers.

Let's say that the Pivot Table is called PT_Efficiency and is on a sheet called Efficiency Template. The three different PivotFields are Project, Commodity and Work Center.

There are 5 parameters:

• sht - pointer to the sheet with the pivot table on it
• PTName = pivot table name
• PFName = pivot field name
• piName = the comma separated list of values to select
• NotValue = reverses the display (True checks off boxes instead of turning them on)

Here is how you call it

Please Login or Register  to view this content.

And here is the actual code

Please Login or Register  to view this content.

I have since modified the code as indicated to take care of where people get sloppy on the list definitions and include spaces.

Please Login or Register  to view this content.