Register
Log In
Unanswered
Home
Dashboards
Power Query
Power Pivot
Power BI
June 2, 2016
Offline
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.
Dashboards
Power Query
Power Pivot
Power BI
June 2, 2016
Offline
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!
Most Users Ever Online: 57
Currently Online:
10 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Sethu nath
Jose Alvarado
James Villapudua
Sheri O'Connor
Noreen Anagnos
Connie DeMercurio
Shanna Steinberg
Holly Seamons
Mark Cooper
David Matanana
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
