Paste Error 1004 Occurs in .xla but not .xls 1

[bajo71]

I receive error 1004 Paste Method Failed when trying to run a routine from an Add-In, however on a local file it runs fine. Here's where it breaks.

Set ExclApp = New Excel.Application
ExclApp.Visible = False
Set xlWkBook = ExclApp.Workbooks.Add(xlWorksheet)
xlWkBook.Sheets.Add
xlWkBook.ActiveSheet.Name = "7_Forc_Data"
'Pastes data into new workbook
ActiveWorkbook.Sheets(shtName).Cells.Copy
<<<<xlWkBook.Sheets("7_Forc_Data").Paste>>>>>>>

Any ideas?

Thanks......Mickey

 

[starsky51]

Hi bajo,
Once you set the workbook up as an add-in, the sheets are no longer visible and so paste operations no longer work.
If formatting isn't a big issue, you could use the following code to copy the values across:

For Each cellTemp In ActiveWorkbook.Sheets(shtName).UsedRange
  xlWkBook.Sheets("7_Forc_Data").Range(cellTemp.Address).Value = cellTemp.Value
Next cellTemp

 

[bajo71]

Ok then. You know the add-in worked a few days ago, but for some reason stopped working. There seems to be alot of internal caching of memory/instances etc when using automation; that may have played a role too.

Thank you.....Mickey