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

Question

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.

Answer 1

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