Copy Method of Worksheet class fails with .xlam file

Question

CONTEXT: I have created an Excel Add-In in which there is a worksheet named "Mother". When the user clicks a button from any workbook in which the Add-In is installed, I would like the worksheet "Mother" to be copied inside its same workbook and being renamed. In code:

ThisWorkbook.Sheets("Mother").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

WHAT I EXPECT: being the line of code above placed into the .xlam code, I know that ThisWorkbook refers to the Add-In and this is observable through a watcher as well:

So, I expect that a copy of "Mother" will be added to the workbook, at the end.

WHAT HAPPENS: the method simply fails, returning the error Copy Method of Worksheet class failed.

WHAT I HAVE TRIED: By simply looking for the name of the error online I have figured out that, apparently, I cannot copy a hidden worksheet and that's why the method fails. So I have tried:

1) To change the visibility with ThisWorkbook.Sheets("Mother").Visible = True... No success;

2) To activate the workbook before/after to change the visibility, so ThisWorkbook.Activate... No success.

I'm not being able to find much documentation online about this, so I'm almost wondering if it's actually possible to edit/show the workbook.xlam, something that I would really need to do for my project.

A BIT MORE OF CONTEXT: The reason why I need to both add the worksheet and show it to the user is that the Add-In, which basically consists in a custom function =myFunction(), needs to be fed by user-inputs for make its calculus. In particular, neither I can write the data in the sheet before to distribute the Add-In (because I don't know the user inputs in advance), nor I can prepare a user-form where to insert the data and show it at running-time (because the user-inputs are usually a big amount of data that can be copied-pasted easily into an Excel spreadsheet, but not inserted manually into a form nor in the function parameters themselves). So:

MY QUESTION: Is it possible to copy (duplicate) a worksheet within a .xlma workbook and activate it after to allow the user data insertion? If yes, where am I being wrong on the above?

Answer 1

Looks like this link would help: http://forums.techguy.org/business-applications/1120165-add-extra-sheet-xlam-file.html.

The operation cannot be performed on add-in workbook, hence you just need to set it as "not add in":

ThisWorkbook.IsAddIn = False
'your copy operation
This Workbook.IsAddIn = True