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

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")



Antonio wrote:

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

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" wrote:

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")



Antonio wrote:

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

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.

Antonio wrote:

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" wrote:

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")



Antonio wrote:

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


--

Dave Peterson