Written by Allen Wyatt (last updated January 16, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Ian has a very large workbook with multiple worksheets and for some reason the file has been corrupted. He can't "Save As" the file. He created a new workbook and copied the individual worksheets into it. However, the largest worksheet contains a lot of shapes and screen shots that did not copy over. Ian wonders if there is a way to copy everything including shapes and screen shots from one workbook to another.
Working with corrupt workbooks can be a challenge. Fortunately, Ian is on the right track—you have to get your data from the corrupt workbook into a new workbook. The thing to remember, though, is that you don't want to right-click on worksheet tabs and use Move or Copy to copy a worksheet from the corrupt workbook to the new workbook. Doing so could very easily copy the corruption to the new workbook as well.
Instead, try this approach:
In doing these steps, it is important to remember that Excel does have some limitations. For all practical purposes these limits are quite high, but it is possible that copying a very large number of objects all at once could exceed those limits.
For instance, Ian mentioned that one of his worksheets contains a lot of screenshots. I routinely make screenshots of my 4K screen, and each image exceeds 7 MB. Put a lot of those in a workbook (however you define "a lot"), and you could run into some size limitation issues fairly quickly. The bottom line is that you'll want to make sure that everything copies over as you expect (step 13).
If this doesn't work, you should consider letting Excel try to repair the workbook. Close the problem workbook and make a copy of it. Within Excel, display the Open dialog box. Navigate to the copy of the problem workbook that you created and then select it. Use the down-arrow next to the Open button and choose Open and Repair. Excel will recover what it can from the workbook.
Additional ideas can be found at this Microsoft website:
https://support.microsoft.com/en-us/office/153a45f4-6cab-44b1-93ca-801ddcd4ea53
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10020) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Need to allow others to contribute to your Excel workbook? It's easy to do if you just share it. This tip provides an ...
Discover MoreExcel has the capability to automatically open workbooks when you first start the program. You may not want to have one ...
Discover MoreWant a quick way to access the workbooks you use most often? You can "pin" the workbooks so they can be opened with a click.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-01-14 03:45:31
Ron S
Sounds like file corruption. That is a pita to deal with.
Option 0 (I had to add a new item to manual numbered list …)
Start Excel in “Safe Mode”. This will disable all addins. Sometimes MS makes code changes / “improvements” that the addin programmer is not aware of. This can cause subtle problems in the addin leading to random seeming errors. Disabling the addins may fix the problem.
Here is the MS help page:
https://support.microsoft.com/en-us/office/open-office-apps-in-safe-mode-on-a-windows-pc-dedf944a-5f4b-4afb-a453-528af4f7ac72
Option 1
Have both workbooks open
Right click on tab to be copied (at bottom of worksheet), select "Copy or Move"
In the dialog, select the other workbook as the "To" destination
In the second part of the Copy/Move dialog select where you want the tab to be pasted.
Swap to the other workbook and check to see if the page is there
Repeat with all tabs
Option 2
Make a copy of the problem workbook
Delete all of the other tabs except the problem tab
Save and close
Open the stripped down workbook.
Try option one again to copy to the new workbook
Try Open and Repair to fix the broken tab
Try option three to copy to the new workbook
Option 3
Try it in smaller chunks.
Select a number of rows, copy, paste into the new workbook.
Save (and close) the new workbook
Confirm that the new content is there.
If it works, keep repeating until you hit a section that doesn't work.
In the problem section try selecting 1 (or just a few) row at a time to copy and paste into the new workbook
Try moving farther down past the problem section to see if you can copy/move more stuff
If that works continue, return to problem section later
Option 4
Go to the MS "Answers Community" and post your problem there
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel
There are some real wizards volunteering there. They may be able to suggest other hacks to fix it. Or they may offer to look at the file and fix it if you give them access to a copy of it.
Option 5
Google "Repair corrupt Excel files"
You'll get lots of hits, ie (picking one at random):
https://repairit.wondershare.com/office-document-repair/6-ways-to-repair-damaged-corrupt-excel-file.html
Try the suggestions, like starting in Safe Mode.
Note, many of these sites will suggesting using their proprietary tools. Do some checking to see if there are problem reports about it or the site. You have to decide for yourself if you believe it is safe to use the tool. Unless I've had experience with that specific company I prefer to not use those tools.
2024-01-13 15:26:48
Dave Bonin
I have a large planning workbook that I've developed and used for several years.
Recently, I started getting corrupted workbook warnings. I'd then quit and revert back to a prior version of the workbook that worked Ok.
It was ten steps forward, then one or two steps back, Rinse and repeat.
I think I found the root cause: I was using the same conditional format on multiple, non-contiguous blocks of cells. D'oh! Since I stripped and then reapplied the conditional formats -- with each format covering only a single block of contiguous cells -- I haven't had any issues.
Yes, this often means I end up with multiple instances of the same format, each with its own block of cells. But so far, no issues.
2024-01-13 08:25:14
Alex Blakenburg
I agree with @Simon, that the Step 2 needs a Ctrl+C. Also Ctrl+A will also not always select the whole sheet. You may need to press Ctrl+A twice. There are instances where even that won't select the whole sheet and you need to click the top left corner of the sheet to select the whole sheet.
To select all the objects it is quicker to select one of the objects then Ctrl+A to select all the objects.
To paste the objects into the same position or close to the same position you can't just go to A1 as indicated in Step 11.
You need to note down the row no of the highest object (row-wise) and the column of the left most object.
Select the cell with this row / column intersection on the target worksheet and paste. ft this is not exactly the right position, then with the objects still selected just use the arrow keys to move the objects the half a cell or so right or down to the exact position.
2024-01-13 07:30:27
Simon Freeman
Does step 2 needs "then press Ctrl-C" after "Ctrl-A"?
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments