How copy cell workbook to workbook?

S

sbrodsky

Can someone tell me why this isn't copying cell 2,3 in 2nd workbook to
cell 5,2 in 1st workbook? It prints the msgbox properly and runs w/o
runtime errors.

Sub Copy_Cell_from_2ndbook_to_1st_book()
Workbooks(1).Worksheets(1).Cells(5, 2).Value =
Workbooks(2).Worksheets(1).Cells(2, 3).Value
MsgBox "Done"
End Sub

Thanks!
 
J

Jack Sons

Write Workbooks1 in stead of Workbooks(1) and Sheets1 in stead of
Worksheets(1) etc.

HTH

Jack Sons
The Netherlands
 
D

Dave Peterson

How would you ever know which workbook is workbooks(1) and which is
workbooks(2)?

I think I'd be much more specific (well, if it's possible)

Sub Copy_Cell_from_2ndbook_to_1st_book()
Workbooks("book1.xls").Worksheets(1).Cells(5, 2).Value = _
Workbooks("book2.xls").Worksheets(1).Cells(2, 3).Value
MsgBox "Done"
End Sub

or even...

Sub Copy_Cell_from_2ndbook_to_1st_book()
Workbooks("book1.xls").Worksheets("sheet1").Cells(5, 2).Value = _
Workbooks("book2.xls").Worksheets("sheet1").Cells(2, 3).Value
MsgBox "Done"
End Sub

I'm betting that your code worked perfectly--it just didn't do it to the
workbooks you wanted.

adding:

msgbox workbooks(1).worksheets(1).range("B5").address(external:=true) _
& vblf & workbooks(2).worksheets(1).range("C2").address(external:=true)

might give you a clue on what got changed.
 
J

JE McGimpsey

They're not arrays, they're collections.

See the "Workbooks Collection Object" and "Worksheets Collection Object"
entries in XL/VBA Help.
 
S

sbrodsky

Thanks Dave + others,

"How would you ever know which workbook is workbooks(1) and which is
workbooks(2)? " was a good question / point.

I was also copying the wrong cells. The working code is now:

Sub Copy_Cell_from_2ndbook_to_1st_book()

' Copy 2nd book's B2 (2,2) to 1st book's E2 (2,5)
Workbooks("Book1.xls").Worksheets("sheet1").Cells(2, 5).Value = _
Workbooks("counts.csv").Worksheets("summary").Cells(2, 2).Value

MsgBox "Done"

End Sub
 
D

Dave Peterson

This portion of your code looks suspicious:
Workbooks("counts.csv").Worksheets("summary").Cells(2, 2).Value
When I open a .CSV file, the worksheet name is based on that file name.

If you change that worksheet name to Summary, then ignore this.

But this is one time I would use:
Workbooks("counts.csv").Worksheets(1).Cells(2, 2).Value
to get that first (and only) worksheet in the .CSV file.
 
Top