Hi Wolfie,

A workbook object is an Excel file.
Workbooks is a collection object consisting of all the workbook objects
currently open in a running instance of Excel.

A worksheet object is a spreadsheet in an Excel workbook.
Worksheets is a collection object consisting of all the worksheet objects in
an Excel workbook.


To copy the values between workbooks, you need to write a macro in a
standard module, something like this:

Sub CopyToOtherBook()
Dim rngSource As Range, rngTarget As Range

Set rngSource = Workbooks("sam.xls").Sheets("Sheet1").Range("A3")
'Substitute the sheetname used here with the name of the sheet containing
the data you want to copy


Set rngTarget =
Workbooks("john.xls").Sheets(rngSource.Offset(-1).Value).Range("A1")
'Substitute the range address used here with the one you want the data
copied to

'Copy the data
rngSource.Copy Destination:=rngTarget

End Sub

HTH
Regards,
GS

"Wolfie" wrote:

I'm trying to copy a value from workbook sam to wokbook john. Workbook john
has multiple sheets. Cell a2 in sam will have the name of the sheet in john
to which I want to paste the value from a3 in sam.

In general, I'm confused about workbook vs. workbooks, worksheet vs.
worksheets, etc.