syntax for fully qualified copy, macro code

bkelly

Active Member
Joined
Jan 28, 2005
Messages
444
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:

Code:
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
Bryan

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

Try this.
Code:
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.
Code:
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

Active Member
Joined
Jan 28, 2005
Messages
444
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,845
Messages
5,544,629
Members
410,626
Latest member
rkmadasu
Top