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!

I don't see where the arrays Workbooks() and Worksheets() are set.
--
Gary''s Student


" wrote:

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!

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

HTH

Jack Sons
The Netherlands

schreef in bericht
ups.com...
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!

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(ext ernal:=true) _
& vblf & workbooks(2).worksheets(1).range("C2").address(ext ernal:=true)

might give you a clue on what got changed.

wrote:

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!

--

Dave Peterson

They're not arrays, they're collections.

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

In article ,
Gary''s Student wrote:

I don't see where the arrays Workbooks() and Worksheets() are set.
--
Gary''s Student


" wrote:

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!

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").Cell s(2, 2).Value

MsgBox "Done"

End Sub

This portion of your code looks suspicious:
Workbooks("counts.csv").Worksheets("summary").Cell s(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.

wrote:

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").Cell s(2, 2).Value

MsgBox "Done"

End Sub

--

Dave Peterson