Power Query Error when loading to an existing sheet

cbabin

New Member
Joined
Mar 22, 2017
Messages
4
In Power Query, I am unable to Close & Load any query to an existing sheet. The error message reads "Cannot return external data to a macro sheet". I have no problem loading to a new sheet.

I am using Office 365 Personal/Windows 10 and it is up to date. I do not have a hidden Personal Macro workbook and I get this error whether or not the workbook contains any macros. I have Googled and found nothing in on this error. Any ideas?

Thanks.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
A macro sheet is not the same as a macro enabled workbook. It sounds strange (I guess you know that). What happens if you run the same query in a new workbook?
 
Upvote 0
A macro sheet is not the same as a macro enabled workbook. It sounds strange (I guess you know that). What happens if you run the same query in a new workbook?

Matt,

Thanks so much for your reply. I get the same result (the error message) every time I try to close & load any query to an existing sheet - even a new workbook. My workaround has been to load to a new sheet, then cut & paste back to the original existing sheet. This works, but is kind of a pain.

I do not get the error message on another PC with the same version of Excel and OS. This leads me to believe that there may be some setting on the first PC that is causing this, but I have no clue what it might be.
 
Upvote 0
I have the same problem with Excel 2016 MSO 16.0.4639.1000 32-bit
This looks like some kind of deep legacy bug from excel 4 code.

My work around is to load the table to a new sheet then cut and paste it to where I want it.
 
Upvote 0
Ran an Office repair, but no luck - no change.

Here's how I (accidentally) solved this problem. When my Office 365 subscription came up for renewal, I opted to do a complete re-install of Office. I had previously tried doing a repair, but this did not work. Uninstalling and reinstalling Office worked. Problem has not happened since.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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