I need a macro that will match the Emp ID and Case ID on both sheets. If found, the macro should CUT from Sheet 2 and paste in the next blank row of sheet 1, so for the example above, after running the macro the table on sheet 1 should look like this.

On Error Resume Next
Set r2e = r2.Offset(0, 4).SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
If Not r2e Is Nothing Then
Set r2eRow = r2e.EntireRow
Set r2eRow = Intersect(r2eRow, r2.Resize(1, 4).EntireColumn)
r2eRow.Copy r1(r1.Count).Offset(1, 0)
r2eRow.EntireRow.Delete
End If
r2.Offset(0, 4).ClearContents
End Sub

That was tested with your data and worked for me.

I delete entire rows so that could be a drawback if you have other data on the same row as a row being deleted (in the second sheet).

--
Regards,
Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: One last question Tom, what if I want to change the match criteria? instead of emp Id, I want to match it with Fname, basically from a different column.

This looks for a match for Fname and CaseID. So when a match is found, this formula returns a #N/A and when not it returns a "" and the cell looks Blank. This allows the code to identify the matching rows by using column L and doing a Goto Special then choosinge formula and error value.

[this is untested, so I could have made a typo - but you should get the idea]

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want.
[Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]