ActiveSheet.Paste - Error help?

[dk_]

This short macro below, gets stopped with an error in the last line
which reads 'ActiveSheet.Paste', if the referenced workbook,
(test1.xls), is already open when the macro is run.

This macro runs without errors on both the Windows Excel97 and the Mac
Excel98 platforms when the referenced workbook 'test1.xls", is not open
at the time that this macro is run.

(This is the beginning of a longer macro.)

How can this procedure be fixed to make it so the macro can run even if
the "test1.xls" workbook is already open?

This macro is started while viewing an active workbook with downloaded
quotes.


.....Macro starts below here....

Sub TEST_Quote_Copier()
Application.ScreenUpdating = False
Range("A1:I120").Copy

mydir = ActiveWorkbook.Path
Workbooks.Open FileName:=mydir & Application.PathSeparator &
"test1.xls"

Sheets("xyzQuotes").Activate
Range("A10").Select
ActiveSheet.Paste
End Sub


.... am enjoying the learning curve.

Thanks for all the help.

-Dennis

 

[Guest]

change it to be this procedure

Sub TEST_Quote_Copier()
Dim x As Workbook
Dim mydir As String
Application.ScreenUpdating = False
Range("A1:I120").Copy

mydir = ActiveWorkbook.Path

For Each x In Workbooks
If x.Name = "test1.xls" Then
x.Activate
Sheets("xyzQuotes").Activate
Range("A10").Select
ActiveSheet.Paste
Exit Sub
End If

Next
Workbooks.Open Filename:=mydir & Application.PathSeparator & "test1.xls"
Sheets("xyzQuotes").Activate
Range("A10").Select
ActiveSheet.Paste
End Sub

 

[Don Guillett]

Try this which does not need to open the destination wb.
Sub copytoopenwbfromactivewb()' ONE line
Range("c5:c8").Copy Workbooks("yourfile.xls").Sheets("sheet1").Range("a1")
End Sub
or this for ease of reading with continuation _ character
Sub copytoopenwbfromactivewb()' split line
Range("c5:c8").Copy _
Workbooks("yourfile.xls").Sheets("sheet1").Range("a1")
End Sub

 

[dk_]

Walid,

Your code below, gets stopped at the same place as my code below, when
my 'test1.xls' file is open.

It does run the same as my code below when my test1 file is closed.

Thanks.

-Dennis

 

[dk_]

Don,

Your code works, but ONLY when the file my 'test1.xls' file is already
open.

When my file 'test1.xls' is closed, Excel gets stopped with:

Run-time error '9'
Subscript out of range.

How could I include your example with an 'IF' test, to run your code if
my 'test1.xls' file is open, OR run my code, posted below, if the
'test1.xls' file in NOT open?

Thanks for the instruction.

I will use your example in other types of copying data situations to be
used for files that are already open.

-Dennis