Pasting within an Addin
 

Hello!

I have the following code within an addin:

ThisWorkbook.Sheets("Sheet1").Range("A1").Copy
ThisWorkbook.Sheets("Sheet2").Range("B2").PasteSpe cial _
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

Pasting within an Addin
 

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.

--
Regards,
Tom Ogilvy


"Dave Ramage" wrote in message
...
Hello!

I have the following code within an addin:

ThisWorkbook.Sheets("Sheet1").Range("A1").Copy
ThisWorkbook.Sheets("Sheet2").Range("B2").PasteSpe cial _
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

Pasting within an Addin
 

Try this:

application.screenupdating = false
Application.isaddin = false

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

application.isaddin = true
application.screenupdating = true

Hope this work

--
Abh
-----------------------------------------------------------------------
Abhi's Profile: http://www.excelforum.com/member.php...fo&userid=1585
View this thread: http://www.excelforum.com/showthread.php?threadid=27328