This time round I'm the one that is confused. I mentioned that the workbooks
are, MyWorkbook1 in Sheet1 starting cursor position Column2 Row 3
(destination) and MyWorkbook2 (source) - no other parameters are needed for
the latter. Both are open. Using those information it should shorten the
codes somewhat.
Also, after a source name is found, can you do an offset like,
activecell.offset(0, 1).range("A1:A5").select to copy the 5 cells on the
right, bring it back to MyWorkbook1, do another offset, activecell.offset(0,
1).select and paste?Then reposition the cursor if needed.
The first line that stumped me is:

Set srcNamesList = srcWS.Range(srcSheetNamesCol & srcSheet1stNameRow _
& ":" & srcWS.Range(srcSheetNamesCol & Rows.Count).End(xlUp).Address)

What are the items that I have to replace? I never learned VBA but know how
to use Excel's automatic macro procedure. So, if you can modify some of the
lines it would be a great help. Thanks!

"JLatham" wrote in message
...
At the risk of getting way ahead of myself, I wrote the following code
based
on what I am guessing you really want, and my idea of that is:
match names on 2 sheets in 2 different workbooks, and when a match is
found,
then copy 5 COLUMNS next to the match in the second workbook into the
first
one.

So if you find a match in 2nd workbook at B33 (name in first WB at B4)
then
copy C33:G33 from 2nd workbook into C4:G4 of the first one. The various
column IDs are definable in the code.

Here's that code (note that it prompts you for the second workbook, so
that
one should not be open when you run the macro). I've tried to keep the
lines
short so that the system here doesn't mess things up. Check after you
copy
the code for any red lines in your code, that just means that whatever is
red
probably should be at the end of the line above it.


Sub CopyFrom2ndWorkbook()
'change these Const values as required
'name of the worksheet in this workbook
'to copy data into, is also the sheet
'with the source list of names
Const destinationSheetName = "Sheet1"
'first row with names in it
Const destSheet1stNameRow = 2
'column with the names in it
Const destSheetNamesCol = "B"
'1st column to copy information into
Const destSheet1stCopyCol = "C"
'last column to copy information into
Const destSheetLastCopyCol = "G"

'information about worksheet in the other
'workbook (one that will be opened and copied from)
Const sourceSheetName = "2ksPublicAssistance (3)"
Const srcSheetNamesCol = "B"
'first row with names in it
Const srcSheet1stNameRow = 2
'first column to copy from
Const srcSheet1stCopyCol = "C"
'last column to copy from
Const srcSheetLastCopyCol = "G"

Dim srcWB As Workbook ' will be copy from workbook
Dim srcWS As Worksheet ' will be copy from sheet
Dim srcNamesList As Range
Dim anySrcName As Range
Dim srcCopyRange As Range
Dim srcWBName As String

Dim destWS As Worksheet ' sheet in this workbook
Dim destNamesList As Range
Dim anyDestName As Range
Dim destCopyRange As Range

'prompt user to open the other workbook
srcWBName = Application.GetOpenFilename
If UCase(Trim(srcWBName)) = "FALSE" Then
'user cancelled the get filename operation
Exit Sub
End If
Application.ScreenUpdating = False
'open w/o updating links and as Read Only
Application.DisplayAlerts = False
Workbooks.Open srcWBName, False, True
Application.DisplayAlerts = True
'opened book becomes active
Set srcWB = ActiveWorkbook
'back to this workbook
ThisWorkbook.Activate
Set srcWS = srcWB.Worksheets(sourceSheetName)
Set srcNamesList = srcWS.Range(srcSheetNamesCol & srcSheet1stNameRow _
& ":" & srcWS.Range(srcSheetNamesCol & Rows.Count).End(xlUp).Address)

Set destWS = ThisWorkbook.Worksheets(destinationSheetName)
Set destNamesList = destWS.Range(destSheetNamesCol & destSheet1stNameRow
_
& ":" & destWS.Range(destSheetNamesCol & Rows.Count).End(xlUp).Address)

'note that in VB, case is important: Bill does not = BILL
For Each anyDestName In destNamesList
For Each anySrcName In srcNamesList
If anySrcName = anyDestName Then
'have a match
'NOTE: number of columns in each range must be same
'not their addresses, but total number of columns, as
'C#:G# = 5 columns
Set srcCopyRange = srcWS.Range(srcSheet1stCopyCol _
& anySrcName.Row _
& ":" & srcSheetLastCopyCol & anySrcName.Row)
Set destCopyRange = destWS.Range(destSheet1stCopyCol &
anyDestName.Row _
& ":" & destSheetLastCopyCol & anyDestName.Row)
destCopyRange.Value = srcCopyRange.Value
'we can quit now that we found the match
Exit For ' exit the anySrcName loop
End If
Next
Next
'housekeeping
Set destNamesList = Nothing
Set srcNamesList = Nothing
Set srcWS = Nothing
Set destWS = Nothing
'close the other workbook, do not save changes
Application.DisplayAlerts = False
srcWB.Close False
Application.DisplayAlerts = True
Set srcWB = Nothing
MsgBox "Copy from:" & vbCrLf & srcWBName & vbCrLf & "Completed", _
vbOKOnly + vbInformation, "Task Finished"
End Sub



"Tom" wrote:

Wow! You are very methodical and it performs exactly what I hope it would
do. Thank you very much.
I have a slightly similar request. This time getting back some data. Much
obliged if you can help with this task as outlined below:

Read a list of column names from two open workbooks - MyWorkbook1 in
Sheet1,
starting from Column2 Row 3, then find the same name in Myworkbook2. Copy
a
set of 5 row values next to its right, then paste it back in MyWorkbook1
next to the right and stop at the end of the list. Skip if a name is not
found.

"JLatham" wrote in message
...
Tom,
In defense of JLGWhiz, you did say "...allows me to update the weekly
wages
for some of these members by..." And that's what his code does. Had
you
initially requested code to update them all, I'm certain he would have
provided exactly that. I'm certain that he overlooked, as I did, the
at-the-end of the post reference to identity numbers on sheet2.

So, try this code in a copy of your workbook and see if it does what
you
want or not. You'll need to change the Const values at the beginning
of
it
after you do the copy to match worksheet names and column IDs in your
workbook before running it.

Sub UpdateWages()
'alter Const values as needed for your workbook
Const wageSheetName = "SheetWithWages" ' sheet1?
Const firstWGIDRow = 2 ' first row w/employee id
Const wsIDColumn = "A"
Const wswagecolumn = "F"
Const amtOfRaise = 0.045 ' 4.5%
Const updateListSheetName = "RaiseListSheet" ' sheet2?
Const lsIDColumn = "A"

Dim wgWS As Worksheet
Dim wgIdList As Range
Dim anywgID As Range
Dim lsWS As Worksheet
Dim lsIDList As Range
Dim anylsID As Range

Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
& wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
For Each anywgID In wgIdList

Set anylsID = lsIDList.Find(What:=anywgID, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If Not anylsID Is Nothing Then
'found a match
wgWS.Range(wswagecolumn & anywgID.Row) = _
wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
End If

Next
Set wgIdList = Nothing
Set lsIDList = Nothing
Set wgWS = Nothing
Set lsWS = Nothing
End Sub




.