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

Question

question

DJGRx-2609 asked Apr 8, '22 emizhang-msft commented Apr 11, '22

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

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.

office-vba-dev office-excel-itpro

image.png (146.8 KiB)

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

emizhang-msft · Apr 08 at 08:06 AM

Hi @DJGRx-2609,
You should provide the VBA code here, so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.

0 ·

DJGRx-2609 emizhang-msft · Apr 08 at 04:01 PM

@emizhang-msft I verified yesterday before posting that I can duplicate the issue by right clicking on the sheet >> select copy (to new workbook) >> select all cells in the worksheet >> paste as values >> save as....

The issue is not related to the macro, however, here is the relevant code (minus variable declarations, of course):

 Sheets(NewShtName(X)).Activate
 ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
      Sheets(NewShtName(X)).Copy
      Cells.Select
      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
 Application.DisplayAlerts = False
     ActiveWorkbook.SaveAs Dir_PriorityLists & NewShtName(X) & "_export", AccessMode:=xlExclusive, ConflictResolution:=xlLocalSessionChanges 'Save file
     ActiveWorkbook.SaveAs Dir_PriorityListsArchive & NewShtName(X) & "_export_" & MyDate, AccessMode:=xlExclusive, ConflictResolution:=xlLocalSessionChanges
     ActiveWorkbook.Close 'Close file
 Application.DisplayAlerts = True

Thanks for any support.

Dexter

0 ·

Answer 1 · 2022-04-09T08:05:25.253Z

DJGRx-2609 answered Apr 9, '22 emizhang-msft commented Apr 11, '22

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

image.png (72.8 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

emizhang-msft · Apr 11 at 10:12 AM

Hi @DJGRx-2609,
I tested in Version 2203 (Build 15028.20160), I cannot reproduce this problem.
I'm trying to check the same version of yours, and I will provide the result soon.

0 ·

question