Copy Paste Based on Value in another sheet

[surajkartha]

Hi

I actually need a macro that would copy data from one sheet to another based on the condition that the value in one sheet is equal to the value in another sheet.

For example, the workbook I am working on has two sheets namely "Sheet1" and "Sheet2". Sheet1 consists of three columns which are "Employee Name", "Pay" and "Arrears". I created a macro to automate the copy/paste functionality. Now the problem is that the raw data for "Arrears" is in another workbook, so I added another piece of code to my macro that can copy data from the workbook that has the raw data and pastes it to "Sheet2". Now I need a macro to check if the "Employee Name" in Sheet2 matches with the "Employee Name" in Sheet1 and if yes, it should copy/cut and paste the value from Sheet2 to Sheet1 under the column "Arrears" corresponding to that employee whose name is mentioned.

Please help me out.

Regards
Suraj

 

[pedie]

I think VLOOKUP can solve this problem...

Or maybe something like this...

[/FONT]
[FONT=Courier New]Sub test1()
Dim myx  As String
Dim lr, lr2 As Long
Dim myfound, c As Range
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
For Each c In Sheet1.Range("A1:A" & lr)
 If c.Value <> "" Then
  myx = c.Value
  Set myfound = Sheet2.Range("A1:A" & lr2).Find(What:=myx, LookIn:=xlValues, LookAt:=xlWhole)
   If Not myfound Is Nothing Then
    myfound.Resize(0, 3).Copy c.Offset(0, 4)
   End If
 End If
Next c
End Sub

 

[surajkartha]

Thanks for the reply
The problem is that the size and names of the employees keep on changing due to a lot of internal and external factors why I am having to choose a different mode other than VLOOKUP. The macro for some reason isn't working

I need a macro that something looks like this:

Is Sheets("Sheet2").Range("F2").Value = Sheets("Sheet1").Range("A2").Value [F2 is what has got the employee name and G2 contains Arrears]

If yes, copy and paste F2:G2 from Sheet2 to C2:D2 in Sheet1

else

Check A3 in Sheet1, so on and so forth

Any ideas?

 

[surajkartha]

Hey Pedie, thank you so much for your inputs , in fact, I gave it a thought and guess what, as you said, VLOOKUP did the magic, here's the code for anyone else who is facing a similar issue:

Dim ab As Integer
ab = Application.WorksheetFunction.CountA(Range("A2:A65536"))
For br = 1 To ab
Range("F2:F" & ab + 1).Value = "=VLOOKUP(A2,Sheet2!$F$2:$G$" & ab + 1 & ",2, FALSE)"
Next br

Thanks

 

[pedie]

You're welcome...

defining lastrow like this should be better instead if counting it, because it can have blank sometimes....

[/FONT]
[FONT=Courier New]lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]