Mac Excel 2011, Pivot Table from multiple workbooks

[vanclute]

Please forgive if this has been covered, I searched but there were *so* many posts on the general topic and the first bunch I looked at were all Windows-only solutions, so I decided I just needed to post anew.

I'm on Mac Excel 2011, so there is no Pivot Table Wizard. I have 4 workbooks, each with an identical table (layout that is, data is of course unique in each) that I would like to summarize in a single pivot table in a different workbook.

Is it possible to create a pivot table in one workbook from data contained in tables in 4 other workbooks using Mac Excel 2011 and preferably without requiring VB, and if so... how would I go about this?

Thanks!

 

[vanclute]

well so much for that, Disabling the macro that was reacting to CMD-OPT-P didn't help. Still does nothing at all.

 

[starl]

Without the wizard? no. Without VBA - use the wizard.

 

[vanclute]

And if there was such a Wizard, I would happily use it. But since there isn't... I need to find some other solution. I'm just not strong enough with VBA to wrangle something like this on my own, so unless someone has code they can just hand to me that does exactly what I need and works on Mac 2011, I need to either get this wizard to appear, or find another way.

Trust me I'd be thrilled if this wizard was available, but for no reasons at all that I can see, it just isn't.

 

[vanclute]

Something really weird is going on here. I have changed the shortcut for the Macro that was giving me a conflict (it's now Control+Command+9) and saved the Personal Workbook, yet still every time I type CMD-OPT-SHIFT-P it tries to execute this macro. What the heck...

 

[vanclute]

OK, just noticed something by chance that is really weird. iTunes was flashing the Genius symbol over the "Rewind" button every time I hit CMD-OPT-P (had iTunes visible in the background this time). So clearly it's somehow receiving the keystroke. So I quit it, and now Excel does not respond to any of my previously attempted keystrokes (including ignoring the Macro, as it should).

This just gets weirder and weirder. I think I may try a reboot next in case my environment is in a weird state somehow.

 

[vanclute]

OK, mystery solved, but not problem. Even if you edit the shortcut by changing the comment in the VB editor, that does NOT actually change the shortcut! To do that, you have to open the Macro dialog, select the macro in question, and click on Options, to see the shortcut and change it. Go figure.

So now that I've really changed it (and rebooted for good measure), I definitely no longer have any keystroke conflicts. However, still no Wizard or any reaction at all to CMD-ALT-P.

 

[vanclute]

Hey wait a second... on the Ribbon in the Data tab, there's something labeled "Consolidate". Could that be what I need? Kinda almost seems like it could be...

 

[starl]

1. No, changing the comment in the code would not update the keyboard shortcut. It's just a comment. Has no functionality attached to it.
2. Consolidate won't help with pivot tables.. what it does is combine multiple sources and create a new data table. Perhaps it's enough for what you need - I do not know. But it will not create a pivot table.
3. The keyboard shortcut should work - obviously your system has over-ridden Excel's commands. On a Pc, opening in safemode is extremely easy and would allow for testing of this theory. Alas, you are on a Mac.
4. You can also manually open the wizard through the VBE. To do this
a. Open the VBE
b. Open the Immediate Window (View, Immediate Window)
c. type the following: application.Dialogs(xlDialogPivotTableWizard).Show
d. press ENTER. This will open the wizard in Excel. You can put it into a sub in your personal workbook for repeated usage.

 

[vanclute]

4. You can also manually open the wizard through the VBE. To do this
a. Open the VBE
b. Open the Immediate Window (View, Immediate Window)
c. type the following: application.Dialogs(xlDialogPivotTableWizard).Show
d. press ENTER. This will open the wizard in Excel. You can put it into a sub in your personal workbook for repeated usage.

Hot diggity! That worked perfectly. No idea why I have to do it, but whatever... it works!! Will put it in a macro. THANK YOU!!

 

[starl]

Word of advice - stay away from Excel 2016 on the Mac, specifically when it comes to macros. They broke it. Microsoft recommends programming on a PC and then testing/adjusting on a Mac as needed.