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
