my workbook has 50 worksheets, one for each state.
Each sheet has identical structure in cells a1:b6
I need to consolidate them into a pivot table.
One way to do this is to use the consolidation wizard as follows
1) alt d p
2) ins step 1 I select: Multiple consoslidation ranges
3) in step 2a I select: I will create the page fields
4) in step 2b: I add Idaho!A2:B6
I specify I want 1 page field
I specify that that page field's name is "Idaho"
5 I then repeat step2b 49 more times.
Naturally, this is very cumbersome. I might use a macro recorder to automate the 50 steps, but I wonder if anyone knows a better way?
I tried using a 3d reference like 'Idaho:Wyoming!a2:b6' but that did not work