excelvbaxlsxworksheetxla

VBA copy sheet from a .XLSX and insert it into a .XLA file

I'm trying to copy a Sheet from a .XLSX file as a hidden sheet in a .XLA file.

I only managed to copy from .XLA -> XLSX, but not XLSX -> XLA.

Take a look at my two functions:

Sub copyFromXLAtoXLSX()
    temp.Sheet1.Copy Workbooks(1).Sheets(1)  
End Sub

Sub copyFromXLSXtoXLA()
    ' It gives me the error: Run-time error '1004': Application-defined or object-defined error  
    Workbooks(1).Sheets(1).Copy temp.ThisWorkbook.Sheets(1)  
End Sub

(left) copyFromXLAtoXLSXand (right) copyFromXLSXtoXLA

Any help is really welcome. Thanks!

Solution

  • The XLA sheets collection cannot be accessed for adding a new sheet as long as .IsAddIn=True. Try to set the XLA workbook to .isAddIn = False, make your copy and then set it back to True.

    I think it should be (to be tested): 

    Sub copyFromXLSXtoXLA()
    Workbooks(1).IsAddIn = False '<-- set it false
    Workbooks(1).Sheets(1).Copy temp.ThisWorkbook.Sheets(1)  
    Workbooks(1).IsAddIn = True '<-- set it back to true
End Sub