We found a problem with some content: Copying Sheets with Tables and Total Rows

DJG_Rx 1 Reputation point
2022-04-08T00:57:16.857+00:00

Version 2205 (Build 15130.20000)

Hello,

I have a macro which cycles through several sheets within a workbook, copies those specific sheets into a new workbook (1 new workbook for each sheet), selects & copies all cells and pastes as values. The intent is to convert all formulas (which point to the original workbook) into values.

The problem is, when a sheet contains a table (specifically one with a total row), the "We found a problem" message pops up & a repair is completed when the user opens the workbook saved via the macro.

The only repair I can see is that the total row is removed from the table.

This export process is designed for other end-users to quickly retrieve portions of the originating workbook that are pertinent to them & without the hassle of waiting for calculations, moving through several sheets/tabs, etc. It is quite annoying for them to deal with the message and repair process. And I cannot save over the original file after the repair is completed.

I can obviously select just the table object and copy/paste-as-values, but then all data outside of it remains as is with formulas intact.

Any ideas on how to complete this process of copying a tab with a table (& table row) and overwrite everything as values without ending in the "We found a problem" message?

Below is a screen shot showing the table after the repair is made when opening the workbook.

191127-image.png

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,669 questions
{count} votes

1 answer

Sort by: Most helpful
  1. DJG_Rx 1 Reputation point
    2022-04-09T08:05:25.253+00:00

    I spent time creating a sample file.... removing any company identifiable information and such... recreated the issue by selecting all cells and pasting as values. The result is what I described above.

    And yet, I cannot upload an excel file into a MS support forum???????

    I'm just grateful I can at least upload a meme

    191541-image.png