fredgcook
asked on
Excel 2010 - copying cells - Cannot complete this task with avaiable resources.
I am trying to use a desktop computer with Windows 8.1 and am getting an error message when trying to copy cells from one tab to another in Excel 2010. I do not have the problem on my Win 7 laptop or a different Win 7 desktop.
The issue:
I have a spreadsheet with a pivot table that I refresh weekly from an Access database. Each week I create a new tab by right clicking on the previous week's tab and selecting Copy. After renaming the new tab with the date, I go back to the previous week's tab, click the top left corner of the spreadsheet so that the entire sheet is selected, right click, select Copy, and then Paste Values. The last step prevents the previous week's data from getting updated when I refresh the pivot table on the current week's tab. There is no problem doing this with the exact same file on my Win 7 laptop or Win 7 desktop. It only happens on the Win 8.1 desktop. I initially had Office Pro 32-bit on the Win 8.1 desktop but I uninstalled it and installed the 64-bit version hoping that would resolve the issue, but it has not. The problem occurs when I select Copy - I get the message: Excel cannot complete this task with available resources. Choose less data or close other applications. This happens after a reboot with no other applications open. Also as I mentioned above it happens with 32-bit and 64-bit Excel.
Configuration details:
My laptop has Win 7 SP1 64-bit, 6 GB RAM and Office 2010 Pro 32-bit. My desktop that works has Win 7 SP1 32-bit, 4 GB RAM and Office 2010 Pro 32-bit. The Win 8 desktop has Win 8.1 Pro 64-bit, 12 GB RAM and Office Pro 64 bit.
I hope I have provided enough detail to get some help with this issue. Thanks for your help.
The issue:
I have a spreadsheet with a pivot table that I refresh weekly from an Access database. Each week I create a new tab by right clicking on the previous week's tab and selecting Copy. After renaming the new tab with the date, I go back to the previous week's tab, click the top left corner of the spreadsheet so that the entire sheet is selected, right click, select Copy, and then Paste Values. The last step prevents the previous week's data from getting updated when I refresh the pivot table on the current week's tab. There is no problem doing this with the exact same file on my Win 7 laptop or Win 7 desktop. It only happens on the Win 8.1 desktop. I initially had Office Pro 32-bit on the Win 8.1 desktop but I uninstalled it and installed the 64-bit version hoping that would resolve the issue, but it has not. The problem occurs when I select Copy - I get the message: Excel cannot complete this task with available resources. Choose less data or close other applications. This happens after a reboot with no other applications open. Also as I mentioned above it happens with 32-bit and 64-bit Excel.
Configuration details:
My laptop has Win 7 SP1 64-bit, 6 GB RAM and Office 2010 Pro 32-bit. My desktop that works has Win 7 SP1 32-bit, 4 GB RAM and Office 2010 Pro 32-bit. The Win 8 desktop has Win 8.1 Pro 64-bit, 12 GB RAM and Office Pro 64 bit.
I hope I have provided enough detail to get some help with this issue. Thanks for your help.
On the tab with the pivot, press End then Home or "Ctrl+End"; if the Pivot is the only thing on the tab this should take the cursor to the Grand Total, bottom right of the pivot. If not and it goes way beyond, it could be an issue with the used area of the sheet. Try selecting only the pivot area rather than the whole sheet.
If the cursor does go way beyond the bottom right of the pivot, you can get rid of the surplus rows and columns quite simply. Go to the column to the right of the pivot and press Shift + End then Home or Shift+Ctrl+End to select the surplus columns and delete the columns, not just press delete key but actually remove the columns. Repeat for the surplus rows below the pivot. The Used area will only be reset when you save so go back to the top of the sheet and Save. The Ctrl+End should now go to the bottom of the pivot.
Thanks
Rob H
If the cursor does go way beyond the bottom right of the pivot, you can get rid of the surplus rows and columns quite simply. Go to the column to the right of the pivot and press Shift + End then Home or Shift+Ctrl+End to select the surplus columns and delete the columns, not just press delete key but actually remove the columns. Repeat for the surplus rows below the pivot. The Used area will only be reset when you save so go back to the top of the sheet and Save. The Ctrl+End should now go to the bottom of the pivot.
Thanks
Rob H
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I accepted my comment because there was no solution to the issue only work around.
The only thing I can think of is maybe the "temp" environment variable is messed up, so Win8 doesn't have enough temporary disk space to work with.
A possible workaround is to right-click on the tab and copy the entire tab. XL will rename the named range on the new tab, but the pivot table will probably still point to the original tab. You should be able to rename the new tab as your backup.