Dealing with different workbooks

[minghong]

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 :


Workbooks(" book1 .xls").Sheets("sheet1").Range (.Cells(2, 3), .Cells(1000, 3)).Value = Workbooks("book2.xls").Sheets("sheet2").Range(.Cells(28, 7), .Cells(1000, 7)).Value

It can not work anymore. It showed that there's mistake for the use of "Cells" ,but I don't know what wrong here and if there's possible solution for that.
Please help! Thanks!: pray2:

[Jacob Hilderbrand]

You can't use .Cells unless you are using a With statement.


Workbooks("book1 .xls").Sheets("sheet1").Range(Cells(2, 3), Cells(1000, 3)).Value = _
Workbooks("book2.xls").Sheets("sheet2").Range(Cells(28, 7), Cells(1000, 7)).Value


See if that works for you.

[minghong]

And another problem is: I tried to count the number of data in a particular row in another closed workbook(book1), and I used the code in book2 :


Workbooks.Open Filename:="C:\Documents and Settings\book1.xls"
Workbooks("book1.xls").Activate
With Workbooks("book1.xls").Worksheets("Sheet1")
.Activate
i = Application.count("B50:IV50")
j = i / 2
End With
but it showed that the value of j is 0 instead of the actual value. Again, please help!:doh:

[minghong]

Thanks! Jedi, But since I already wrote the code with two workbooks at each side of the equal signal, I don't know how to use " with statement" to describe it. Could you show me how to do that? Thanks a lot!

[Jacob Hilderbrand]

You need to specify that B50:IV50 is a range, otherwise it is just a string.



i = Application.WorksheetFunction.Count(Range("B50:IV50"))


You can't use With to describe both ranges at once, and you can't use .Cells unless you are using a With statement. Since you are not using a With statement, you need to make some changes like my example.

[Zack Barresse]

minghong, I have moved your thread to the Excel Help forum, from the Pay-for-Answer forum; that forum is if you would like to pay somebody to do the job for you. If this has been a mistake please let me know and I will rectify the situation.

[minghong]

Thanks! Jacob. I will try it again!

Thank you firefytr, I'd like to do it myself. Actually, I've already finish most part of it. Please cancle the movement of my thread.

[Zack Barresse]

Hello minghong, do you mean you would like to leave the thread in this forum (which is a do-it-yourself forum)?

[minghong]

hi! firefytr, yes, I'd like to leave the thread in this forum. I just want to get some ideas to help me finish it.





Hello minghong, do you mean you would like to leave the thread in this forum (which is a do-it-yourself forum)?

[minghong]

You can't use .Cells unless you are using a With statement.


Workbooks("book1 .xls").Sheets("sheet1").Range(Cells(2, 3), Cells(1000, 3)).Value = _
Workbooks("book2.xls").Sheets("sheet2").Range(Cells(28, 7), Cells(1000, 7)).Value


See if that works for you.

Thanks! Jacob. I tried your example and it didn't work. The error message says:" Run-time error '1004' application-defined or object-defined error". Do you have any other ideas of how to use "cells" without "With statement"? Thanks a lot!

The problem is I want to use a loop to read the data from book2 to book1 one and another, so I was thinking to use the following code which doen't work at this time.:think:


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

[Jacob Hilderbrand]

Try this.


Workbooks("Book1.xls").Sheets("Sheet1").Range(Cells(2, 3).Address & _
":" & Cells(1000, 3).Address).Value = _
Workbooks("Book2.xls").Sheets("Sheet2").Range(Cells(28, i).Address & _
":" & Cells(1000, i).Address).Value


Making sure that the workbook names and sheet names are correct.

[minghong]

Try this.


Workbooks("Book1.xls").Sheets("Sheet1").Range(Cells(2, 3).Address & _
":" & Cells(1000, 3).Address).Value = _
Workbooks("Book2.xls").Sheets("Sheet2").Range(Cells(28, i).Address & _
":" & Cells(1000, i).Address).Value


Making sure that the workbook names and sheet names are correct.


Thank you very much! Jacob. It works pretty well this time. Thank you!!!!!:beerchug:

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care