Hi there,
I created a macro called "Regions" which loop through slicer "Region" and select different combinations (1 through 6) of the slicer from the table Regions
| Regions | ||||||
| 1 | 2 | 3 | 4 | 5 | 6 | |
| A | 1 | 0 | 0 | 0 | 0 | 0 |
| B | 0 | 1 | 0 | 0 | 1 | 1 |
| C | 1 | 0 | 1 | 0 | 0 | 0 |
| D | 0 | 0 | 0 | 1 | 0 | 0 |
| E | 0 | 0 | 1 | 0 | 1 | 1 |
then values of the pivot table for different slicer combinations are copied into the separate table under the pivot table.
This macro works.
However, I created a macro called "Years" (which is identical to the macro Regions) which is supposed to loop through slicer "Year" and select different combinations (1 through 5) of the slicer from the table Years
| Years | |||||
| 1 | 2 | 3 | 4 | 5 | |
| 2000 | 0 | 0 | 0 | 0 | 0 |
| 2001 | 0 | 1 | 0 | 0 | 0 |
| 2002 | 0 | 0 | 1 | 0 | 0 |
| 2003 | 0 | 0 | 0 | 1 | 1 |
| 2004 | 1 | 0 | 0 | 1 | 1 |
| 2005 | 0 | 1 | 0 | 1 | 1 |
And this macro doesn't work. It's so strange as what I've done is only changed cell ranges and slicer cache name in the macro which works perfectly.
Could someone help me to figure out what's wrong and how to fix that. See the Excel file attached.
Thanks in advance.
Hi there,
sorry to bother you again.
I managed to fix the issue on my own.
The problem was with variable type. Despite I changed Years format to text in the pivot table source table VBA perceived them as numbers so I added apostrophe in front of each year.
So now it works!
