I am trying to create a macro that would look at each
value in Column C of Page 1, and read the data in the 3
columns to the right of Column C, then go to Page 2 and
find the corresponding value in Column A, and then copy
the data from Columns D, E, F of Page 1 to Columns B, C, D
of Page 2. Page 2 may have multiple cells in Column A
that all have the same value, but the values in Column C
of Page 1 will never be repeated. Can someone help me
accomplish this task? Thank you very much!
JBR

One way:


Public Sub CopyOver()
Dim searchRange As Range
Dim cell As Range
Dim found As Range
Dim foundAddr As String

Application.ScreenUpdating = False
Set searchRange = Sheets("Sheet2").Range("A:A")
With Sheets("Sheet1")
For Each cell In .Range("C1:C" & _
.Range("C" & Rows.Count).End(xlUp).Row)
Set found = searchRange.Find( _
what:=cell.Value, _
LookIn:=xlValues, _
lookat:=xlWhole, _
MatchCase:=False)
If Not found Is Nothing Then
foundAddr = found.Address
Do
cell.Offset(0, 1).Resize(1, 3).Copy _
Destination:=found.Offset(0, 1)
Set found = searchRange.FindNext(after:=found)
Loop Until found.Address = foundAddr
End If
Next cell
End With
Application.ScreenUpdating = True
End Sub






In article ,
"JBR" wrote:

I am trying to create a macro that would look at each
value in Column C of Page 1, and read the data in the 3
columns to the right of Column C, then go to Page 2 and
find the corresponding value in Column A, and then copy
the data from Columns D, E, F of Page 1 to Columns B, C, D
of Page 2. Page 2 may have multiple cells in Column A
that all have the same value, but the values in Column C
of Page 1 will never be repeated. Can someone help me
accomplish this task? Thank you very much!
JBR

Another way, if you just want to copy values:

Public Sub copyover2()
Application.ScreenUpdating = False
With Sheets("Sheet2")
With .Range("B1:D" & _
.Range("A" & Rows.Count).End(xlUp).Row)
.FormulaR1C1 = _
"=IF(ISNA(MATCH(RC1,Sheet1!C3,FALSE))," & _
""""",VLOOKUP(RC1,Sheet1!C3:C6,COLUMN(),FALSE) )"
.Value = .Value
End With
End With
End Sub

In article ,
"JBR" wrote:

I am trying to create a macro that would look at each
value in Column C of Page 1, and read the data in the 3
columns to the right of Column C, then go to Page 2 and
find the corresponding value in Column A, and then copy
the data from Columns D, E, F of Page 1 to Columns B, C, D
of Page 2. Page 2 may have multiple cells in Column A
that all have the same value, but the values in Column C
of Page 1 will never be repeated. Can someone help me
accomplish this task? Thank you very much!
JBR