help needed with copy from one workbook to another

[Johan Parlevliet]

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

Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Range(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]

Unqualified range objects in general modules refer to the activesheet.

So this:

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

could be rewritten as this:

Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Range( _
Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Cells(Line_No,1), _
Workbooks(Orig_NameWB).Worksheets(Orig_NameWS).Cells(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.