Copy worksheet INTO add-in

[diakonos1984]

So I've built an add-in so I can access some of my favorite macros from the ribbon, and I'm loving it.

However, my add-in has a worksheet that contains a table I use as a dictionary of sorts.

Occasionally I need to amend my dictionary, and since add-in worksheets aren't directly visible, I copy it into the active workbook, a la

Please Login or Register  to view this content.

That all works great. However, when I'm done amending the dictionary, I want to copy it BACK into the add-in and save it for posterity. I wrote the following code, but it hangs up at wbSource.Sheets("CompTypesDictionary").Copy Before:=wbTarget.Sheets(1) with a Runtime error 1004 "Copy method of Worksheet class failed".

Please Login or Register  to view this content.

What am I doing wrong?

Thanks!

Nathaniel

[AlphaFrog]

You can toggle an Add-in's sheets visible.

From the VBA Editor:

• Select your Add-in in the Projects window (Ctrl-R)
• Remove its password if it has one
• Select the AddIn's ThisWorkbook object in the Project window
• In the Properties (F4) window, change the IsAddin property to False
• You can now see and edit the Add-In's worksheets
• Change the IsAddin property back to True when done editing the sheets.

[Andy Pope]

You can do that in the macro, assuming you have allowed access to VBA project model.

you can rehash this code.

Please Login or Register  to view this content.

Do you really need to delete/copy sheets can you not update range instead?

Please Login or Register  to view this content.

[diakonos1984]

Thanks, Andy, that looks promising. I'll try it out later.

As far as just copying the range, I've thought about that, but it's a named table object, and it gets a little more complicated to copy a table on top of another table and rename, etc.

I noticed that when I copied the whole sheet from one workbook to another, (after first deleting the sheet from the target workbook) that the table name stuck, so that's what I was trying to do.

I will consider both your suggestions.