syntax for fully qualified copy, macro code

[bkelly]

I need to copy a specified block of data from one workbook and sheet to another workbook and sheet. The starting location (top left cell) of the destination copy is not the same as the first. Here is the line of code that I am trying to get working:

Workbooks("First_File").Worksheets("data").Range(Cells(1, 1), Cells(Last_Row, Last_Column)).Copy _
  Destination:=Workbooks("Second_File.xls").Worksheets("CAPD").Range(Cells(Target_Row, Target_Column))

This code solicits the error popup

Run-time error '1004':
Application-defined or object-defined error

I opened First_File and used data sheet "data" to determine Last_Row and Last_Column. Target_Row and Target_Column were extracted from worksheed Second.File.xls but a different worksheet. The worksheet CAPD does exist. I have run out of ways to permutate this statement to make it work.

First_File is a space separated text file and imports okay. I changed it to an excel file in another version, saved the worksheet, then used First_File.xls, with the same results.


How can I do this?

 

[Norie]

Bryan

The problem is probably the unqualified reference(s) to Cells.

Try this.

Dim wsFrom As Worksheet
Dim wsTo As Worksheet

Set wsFrom = Workbooks("First_File").Worksheets("data")
Set wsTo = Workbooks("Second_File.xls").Worksheets("CAPD")
wsFrom.Range(wsFrom.Cells(1, 1), wsFrom.Cells(Last_Row, Last_Column)).Copy _
  Destination:=wsTo.Cells(Target_Row, Target_Column)

Or even this.

Dim wsFrom As Worksheet
Dim wsTo As Worksheet

Set wsFrom = Workbooks("First_File").Worksheets("data")
Set wsTo = Workbooks("Second_File.xls").Worksheets("CAPD")

wsFrom.Cells(1, 1).Resize(Last_Row, Last_Column).Copy wsTo.Cells(Target_Row, Target_Column)

 

[bkelly]

Norie,

How many ways could I possibly say thank you? I don't think I could count them, much less post them. Both methods work as I need.

I don't know why Cells is unqualified. (and when working in VBA, I am not certain what unqualified really means.) My code looks good to me and another guy who spent more than an hour on that one line of code.

Regardless, I now have some working code that I can save and use again later.

Again, and again,
Thank, you.