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

Hi rock,

I have go through the video you attached, this is truly a weird issue.

Based on my understanding on your issue description, this issue seems to be occurring in this specific Excel worksheet, is that correct?

If so, please check whether you have any VBA Macro running on this worksheet. Besides, are you using any add-ins for your Excel application?

Also, I see you were trying to share us with your worksheet, but seems like it does not work out.

Please notice that, this forum does not support uploading file directly in your post. You should upload your file to OneDrive or any other cloud storage services. Then, share it with us.

Using OneDrive for example:

Share files and folders

Regards,

Alex Chen

Answer 5

Thanks, Alex.

Let me try sharing thru dropbox - file. It appears the google link tries to open it in google sheets.

This is not the only file that has this problem. I have a conjecture but not a totally certain way to replicate this issue. It does appear the files I'm having problems with, incl this one, are those created from .xlsm files with excel APIs to retrieve external data which I then copy & paste values of all (and only) API cells via a macro. That is my conjecture.

Thanks