syntax for fully qualified copy, macro code

bkelly

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

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top