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

Public Sub Button3()
'Open Device Types and Numbers Dictionary

    ThisWorkbook.Sheets("SheetCompTypes").Copy After:=ActiveWorkbook.ActiveSheet
    ActiveSheet.Name = "CompTypesDictionary"

End Sub

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".

Public Sub Button4()
'Save and Close Device Types and Numbers Dictionary
    Dim flSheetTest As Boolean 'result of test to see if sheet exists
    Dim wbSource As Workbook 'data is pulled from this workbook
    Dim wbTarget As Workbook 'data is put in this workbook

    Set wbSource = ActiveWorkbook
    Set wbTarget = ThisWorkbook
    
    flSheetTest = MiscSheetExists(shtName:="CompTypesDictionary", wb:=wbSource)
    If flSheetTest = True Then
        'Dictionary exists in open workbook
        flSheetTest = MiscSheetExists(shtName:="SheetCompTypes", wb:=wbTarget)
        If flSheetTest = True Then
            'Dictionary also exists in add-in, and must be overwritten
            wbTarget.Sheets("SheetCompTypes").Delete
        End If
        
'Error on following line!!
        wbSource.Sheets("CompTypesDictionary").Copy Before:=wbTarget.Sheets(1)
        wbSource.Sheets("CompTypesDictionary").Delete
        wbTarget.Save
    Else
        'Dictionary is not open
        Call MsgBox("Component Types Dictionary is not open.  Please open it before attempting to save and close it.", vbExclamation, "Dictionary Not Open")
        
    End If

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.

Public Sub UpdateWordList()
    
    Dim shtOld As Worksheet
    Dim shtNew As Worksheet
    
    Set shtOld = ThisWorkbook.Worksheets("WordList")
    Set shtNew = ActiveSheet
    
    ThisWorkbook.IsAddin = False
    shtNew.Copy Before:=ThisWorkbook.Sheets(1)
    Application.DisplayAlerts = False
    shtOld.Delete
    Application.DisplayAlerts = True
    ThisWorkbook.Worksheets(1).Name = "WordList"
    
    ThisWorkbook.IsAddin = True
    ThisWorkbook.Save
    
End Sub

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

Public Sub UpdateWordList()
    
    Dim shtOld As Worksheet
    Dim shtNew As Worksheet
    
    Set shtOld = ThisWorkbook.Worksheets("WordList")
    Set shtNew = ActiveSheet
    
    shtOld.Range("A1").CurrentRegion.Clear
    shtNew.Range("A1").CurrentRegion.Copy shtOld.Range("A1")
    
    ThisWorkbook.Save
    
End Sub

[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.