Pasting within an Addin

[Dave Ramage]

Hello!

I have the following code within an addin:

ThisWorkbook.Sheets("Sheet1").Range("A1").Copy
ThisWorkbook.Sheets("Sheet2").Range("B2").PasteSpecial _
xlPasteValues, skipblanks:=true

Running this with at least one visible workbook open works
fine, but if no workbooks are open I get a run time
error 'Method PasteSpecial of object Range failed' on the
second line.

This alternative line work fine however:
ThisWorkbook.Sheets("Sheet1").Range("A1").Copy _
Destination:=ThisWorkbook.Sheets("Sheet2").Range("B2")

Unfortunately I need to use the skipblanks and transpose
options, so need to use pastespecial. Any ideas?

Cheers,
Dave

 

[Tom Ogilvy]

Close all visible workbooks

go to the edit menu.

Note that copy and paste are not available. This is the source of your
problem
maybe have the code see if there are any visible workbooks and if not,
create one, then delete it after.

Otherwise, loop through your range and perform the update with the required
behavior by doing assignments.

 

[Abhi]

Try this:

application.screenupdating = false
Application.isaddin = false

ThisWorkbook.Sheets("Sheet1").Range("A1").Copy
ThisWorkbook.Sheets("Sheet2").Range("B2").PasteSpecial _
xlPasteValues, skipblanks:=true

application.isaddin = true
application.screenupdating = true

Hope this work