help needed with copy from one workbook to another
 

I am trying to copy things without using Windows("name").Activate

Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Ran ge(Cells(Line_No,
1), Cells(Line_No, 6)).Copy _
Destination:=Workbooks(New_NameWB).Worksheets(New_ NameWS).Range(Cells(New_Line_No,
1), Cells(New_Line_No, 6))

But this gives an error 1004 and I do not see what I am doing wrong

Unqualified range objects in general modules refer to the activesheet.

So this:

Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Ran ge( _
Cells(Line_No, 1), Cells(Line_No, 6)).Copy

could be rewritten as this:

Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Ran ge( _
Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Cel ls(Line_No,1), _
Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Cel ls(Line_No, 6)).Copy


But I'd use a couple of variables to make typing a little easier:

dim fRng as range
dim tRng as range

with Workbooks(Orig_NameWB).Worksheets(Orig_NameWS)
set frng = .Range(.Cells(Line_No, 1), .Cells(Line_No, 6))
end with

with Workbooks(New_NameWB).Worksheets(New_NameWS)
set trng = .Cells(New_Line_No,1)
end with
'excel will expand the single cell range to match the From Range.

fRng.copy _
destination:=tRng

========
Notice the extra dots in front of .cells. This means that they belong to the
previous with statement object.

If that doesn't work, I'd check each of those variables to see if they were what
I really expected.




Johan Parlevliet wrote:

I am trying to copy things without using Windows("name").Activate

Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Ran ge(Cells(Line_No,
1), Cells(Line_No, 6)).Copy _
Destination:=Workbooks(New_NameWB).Worksheets(New_ NameWS).Range(Cells(New_Line_No,
1), Cells(New_Line_No, 6))

But this gives an error 1004 and I do not see what I am doing wrong

--

Dave Peterson