cannot insert or copy or delete tab

Question

using excel for mac v16.52 on macOS v11.4

was trying to copy a tab into new workbook but could not - neither as a move or "make a copy" - no errmsgs - just no action whatsoever

then tried to isolate that tab by deleting the other tabs - but could delete only 2 of the 4 tabs - one remaining tab refuses to be deleted - no errmsg popped up

tried to insert tab - but got bizzare "this action won't work on multiple selections"

Here is a video demo cannot insert or copy or delete tab

Here is the file cannot insert or copy or delete tab

Thanks a lot for your help

Answer 1

Hi rock,

The __FDSCACHE__ tab is hidden via Macro, it's not like the basic hide/unhide tab function in Excel user interface.

To find this tab, go to Developer tab > Visual Basic. You will see it is listed in the left panel.

And check this sheet's properties > Visible, it's marked as very hidden:

Change the status to visible and close the Visual Basic editor. Then, the sheet will be revealed in the workbook.

As far as I can tell, I didn't find anything related with this issue under the __FDSCACHE__ tab. There are just dozens of meaningless(at least for me) comments in it.

However, according to this message, it seems like something related with the FDS function created by your API vendor?

Also, for the rels folder. Just change the file extension name for your Excel workbook from .xlsx to .zip

Extract all the info from the zip. And navigate to xl > worksheets > _rels folder.

Regards,

Alex Chen

Answer 2

I believe this problem is happening because the very hidden sheet is the first sheet in the workbook. You can do the steps below to work around the problem. These steps will make it so the very hidden sheet is not the first tab in the workbook.

1. In the VB Editor, change the Visible property from xlSheetVeryHidden to xlSheetVisible.
2. Close the VB Editor window.
3. Save your Excel file.
4. Close and reopen the file.
5. Try to reproduce the issue you were having.
6. If the issue doesn't happen, you can move the sheet that should be VeryHidden so that it's not the first tab. Then set it to VeryHidden again and save the file. It should avoid the problem as long as the first sheet is not VeryHidden.

Answer 3

Hi rock,

Thanks for providing me the sample file.

I can confirm the same issue is occurring on my side as well. both under my macOS and Windows environment.

I can't delete those 2 sheets and click on the plus button will return the same error message as yours.

So after that, I did some dig up on workbook.

Interestingly, in the sample file you provided to me, there should be 3 sheets, fds, scoring and __FDSCACHE__

However, if I look into the worksheet _rels folder, only 2 rels files are listed. Normally, it should be equal to the number of your sheets.

Hence, I suspect your workbook should be some how corrupted and I go to Excel > Save your file as a new copy. Just like a magic, it does fix the issue.

No problem adding new tabs or create a copy for the existing one.

And in this new copy of Excel workbook, there are 3 rels file listed.

As I majorly did the troubleshoot work on my Windows desktop, I am not quite sure if this method will work on the macOS or not at this moment. But please give it a shot and keep me informed.

Regards,

Alex Chen

Answer 4

Thank you, Alex. This is very interesting

I tried to save the file as new copy on mac thru File → Save As but the resulting copy still has the same problems - cannot insert, delete or copy tabs. I also tried to unhide tabs by right clicking at a tab but the unhide feature is grayed out. (As an aside: How did you determine that was this _FDSCACHE_ tab? And how did you see those subfolders? can that be done on mac?)

Then I discovered something else:

1. I went back to windows where the file was originally generated. With that original .xlsx file I could insert/copy/delete tabs without problems.
2. I re-downloaded that file onto the the mac - with this fresh copy I could also insert/copy/delete tabs without problems

So what causes the problem? In short, Edit Links.

The file in question has some references to one external file. So in my normal workflow after I downloaded the .xlsx file onto the mac, I have to Edit Links to adjust the file reference. It is AFTER I've edited the links, saved the file and then try to reopen this file on the mac when the problem occurs.

In the file that I provided you, the edit links had already happened and, in order that you don't see a pop up asking for an external file, I have copy/pasted values those affected cells.

On the mac I have just done this Edit Links process a couple of times and the problem can be replicated. However I cannot replicate this problem on the windows side. So this seems to be an issue on the mac version of excel only.

In any event, here is a version of the file (test.210912.xlsx) with links to this simplified file (external.xlsx) in case you want to try it.

Again the steps to replicate on the mac are:

1. open test.210912.xlsx
2. edit links to reference external.xlsx
3. (optionally) verify that you can insert/copy/delete tabs at this point
4. save test.210912.xlsx & close it
5. open test.210912.xlsx
6. try to insert/copy/delete tabs - should fail on mac but works on windows

FYI, if Edit Links does turn out to be a real bug, I can probably side step it by changing my macro to copy

& paste values those links too.

Thanks a lot for your time

Answer 5

Hi Alex, just an update.

It turns out the problem persists even if I don't do Edit Links. All I have to do to experience the problem is:

1. download .xlsx file onto the mac
2. open it
3. save & close it
4. reopen it

Edit: Alex, if you could show me how to reveal, on the mac, the _FDSCACHE_ tab and those _rel folders that would be very helpful as I will need this info to file a bug report with my API vendor. Thanks