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.

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.

i do about the same thing, but would use this as the last line to copy the 1
value

rngTarget = rngSource

without the copy and destination

just a preference


--


Gary


"GS" wrote in message
...
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.

Why don't you just use the Sheet copy menus? Click on the TAB you
want copied. Put in the target Workbook, decide where you want it
put, and be sure to check the box to COPY file, click OK.

Because the OP wants to copy a value from a range, not the entire sheet.

Regards,
GS

" wrote:

Why don't you just use the Sheet copy menus? Click on the TAB you
want copied. Put in the target Workbook, decide where you want it
put, and be sure to check the box to COPY file, click OK.

Hi Gary,

You raise a good point. I too prefer to use that technique. I don't know why
I malfunctioned here, but thanks for mentioning it.

Regards,
Garry

i find myself doing the same thing sometimes, must be subconscious or
something<g

--


Gary


"GS" wrote in message
...
Hi Gary,

You raise a good point. I too prefer to use that technique. I don't know why
I malfunctioned here, but thanks for mentioning it.

Regards,
Garry

I might be just that! I do it constantly when I'm first writing the code.
Then I tune it up afterwards. It's good to keep the dot processing to a
minmum.<IMO

I appreciate the input!
Regards,
Garry

"Gary Keramidas" wrote:

i find myself doing the same thing sometimes, must be subconscious or
something<g

--


Gary


"GS" wrote in message
...
Hi Gary,

You raise a good point. I too prefer to use that technique. I don't know why
I malfunctioned here, but thanks for mentioning it.

Regards,
Garry

Thanks GS, that worked well!

W

"GS" wrote in message
...
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.