dealing with different workbooks

  • Thread starter Thread starter Ming
  • Start date Start date
M

Ming

Hi! All, I'm working on how to transfer data from a workbook to
another. I use the code :

Workbooks(" book1 .xls").Sheets("sheet1").Range("C2:C1000").Value =
Workbooks("book2.xls").Sheets("sheet2").Range("G28:G1000").Value

and it works well. But the problem is I need to use a loop to do
multiple copy and paste data from one to another. I tried to use the
code :

For i=1 to 100
Workbooks("book1 .xls").Sheets("sheet1").Range(.Cells(2, 3),
..Cells(1000, 3)).Value = _
Workbooks("book2.xls").Sheets("sheet2").Range(.Cells(28, i),
..Cells(1000, i)).Value
Next i

It can not work anymore. It showed that there's mistake for the use of
"Cells" . and I revised the code to:

For i=1 to 100
Workbooks("book1 .xls").Sheets("sheet1").Range(Cells(2, 3), Cells(1000,
3)).Value = _
Workbooks("book2.xls").Sheets("sheet2").Range(Cells(28, i), Cells(1000,
i)).Value
Next i

It didn't work. The error message says:" Run-time error '1004'
application-defined or object-defined error".

If there's possible solution for that. Please help! Thanks!
 
First, you've got to watch out for those extra spaces in your workbook names.
And you also changed the size of the range (from row 2 to 1000 to row 28 to
1000. And you're overwriting the same range with 100 different values. I'm
guessing that each of these are just typos in the post and that you wanted to
copy from book1 to book2.

And this essentially copies the same column to 100 different columns. Is that
really what you wanted?

You could do this:

with workbooks("book2.xls").Sheets("sheet2")
For i=1 to 100
.Range(.Cells(2, i),.Cells(1000, i)).Value _
= Workbooks("book1.xls").Sheets("sheet1").Range("C2:C1000").Value
next i
end with

the dots in front of .cells means that that range belongs to the object in the
previous "with" statement.

But you didn't show that you had a previous "with" statement.

That suggestion is equivalent to:

with
For i=1 to 100
workbooks("book2.xls").Sheets("sheet2").Range _
(workbooks("book2.xls").Sheets("sheet2").Cells(2, i), _
workbooks("book2.xls").Sheets("sheet2").Cells(1000, i)).Value _
= Workbooks("book1.xls").Sheets("sheet1").Range("C2:C1000").Value
next i

But takes a lot less typing.

=========
But you might as well get all 100 columns at once.

workbooks("book2.xls").Sheets("sheet2").Range("a2:cv1000").value _
= Workbooks("book1.xls").Sheets("sheet1").Range("C2:C1000").Value
 
Back
Top