Error I cannot figure out

[Guest]

The following code runs well if inventory xls is not open

(...)
Application.DisplayAlerts=True
Columns("A:C").Copy
Workbooks.Open ("c:\documents and settings\antonio\my
documents\inventory.xls")
Worksheets("rent").Activate

Columns("D:F").Select
ActiveSheet.Paste

However, if inventory.xls is already open, when I run the above I get error
at the last line: "Paste failed"


The strange thing is that when running line 3 it does not show the dialog
that inventory.xls is already open.

It seems that it has something to do with reopening inventory.xls but I
cannot figure out where the problem is.

If I have inventory.xls open and I do not reopen it with VBA it works fine.

Thanks,

Antonio

 

[Dave Peterson]

Sometimes just rearranging the order of the copy and open will work wonders:

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
Worksheets("rent").Activate
Columns("D:F").Select

rngtocopy.copy
ActiveSheet.Paste

=======
Or maybe...

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
rngtocopy.copy _
destination:=worksheets("rent").range("D1")

 

[Guest]

Hi Dave,

I have tried the second alternative and it works. Many thanks.

However, I am unhappy about this. It seems to be a bug and I don't like the
feeling of not working with a robust application.

Thanks,

Antonio

 

[Dave Peterson]

There are lots of things that lose the clipboard when you're working in excel
manually.

I'm not sure I would classify it as a bug, but it is something that you (as a
programmer) have to be aware of.

 

[Guest]

Yes Dave, you are right, that is a better way to look at it.

In conclusion, it seems that copying to a range (or other object) is better
practice than using the clipboard. It may be even faster.

Regards,

Antonio

 

[Dave Peterson]

And I've found using variables to represents the objects that I want is nice,
too.

I can set the variable and use it right before I need to. Then I don't have to
worry about any intermediate code destroying what I thought I did.

Yes Dave, you are right, that is a better way to look at it.

In conclusion, it seems that copying to a range (or other object) is better
practice than using the clipboard. It may be even faster.

Regards,

Antonio