Mac Excel 2011, Pivot Table from multiple workbooks

vanclute

Board Regular
Joined
Oct 23, 2002
Messages
218
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!
 
well so much for that, Disabling the macro that was reacting to CMD-OPT-P didn't help. Still does nothing at all.
 
Upvote 0
Without the wizard? no. Without VBA - use the wizard.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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!!
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,882
Messages
6,193,481
Members
453,803
Latest member
hbvba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top