Copying Large, Object-Rich Worksheets from a Corrupted Workbook

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


4

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:

  1. Open your corrupted workbook and a brand-new workbook and position them so you can see both on your screen at the same time.
  2. In the corrupted workbook, select a worksheet. Click a cell within the worksheet (A1 would be great) and then press Ctrl+A followed by Ctrl+C. This copies everything in the worksheet to the Clipboard.
  3. In the new workbook, select a blank worksheet. Click a cell within the worksheet (the same one you selected in the corrupted worksheet) and press Ctrl+V. This pastes everything from the Clipboard into the worksheet.
  4. Examine what was pasted. If there were graphics or objects that were not copied over, then continue with the next step, otherwise jump down to step 14.
  5. In the corrupted workbook, select cell A1 in the same worksheet you selected in step 2.
  6. Press F5. Excel displays the Go To dialog box.
  7. Click the Special button. Excel displays the Go To Special dialog box.
  8. Click the Objects radio button.
  9. Click on OK. Excel selects all the objects in the worksheet.
  10. Press Ctrl+C. This copies the objects to the Clipboard.
  11. Switch to the new workbook and select cell A1 in the same worksheet you used in step 3.
  12. Press Ctrl+V. The objects from the original worksheet should now be placed within the new worksheet.
  13. Examine what was pasted in the new worksheet to make sure that all the objects made it over. If they didn't, then you'll need to copy the remaining objects over one at a time.
  14. Repeat steps 2 through 13 for each of the other worksheets in the original workbook.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Formatting E-mail using AutoFormat

If you copy the text of an e-mail message to a Word document, you may notice that the formatting of the text leaves a lot ...

Discover More

Printing a List of Named Ranges

You already know that you can define names that apply to different ranges of cells and other elements such as formulas. ...

Discover More

Finding Quoted Text in VBA

Macros are created for all sorts of purposes in creating, editing, and processing documents. You might want to use a ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Seeing a Worksheet Thumbnail in Windows

When you save a workbook, you have the opportunity to save a thumbnail image that can be displayed within Windows. Here's ...

Discover More

Strange Message about Others Making Changes in a Workbook

Have you ever tried to save a workbook, only to be notified that someone else has made changes in it? What if you are the ...

Discover More

Making Changes in a Group of Workbooks

If you need to change the same data in a large number of workbooks, the task can be daunting. Here are some ideas (and ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 6?

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"?


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.