This is the macro I'm using now:

Sub copy_it()
Dim r As Range,r1 As Range, r2 As Range
Workbooks ("TMSDL.XLS").Activate
s = InputBox("Enter Search Value:")
For Each r in ActiveSheet.UsedRange
If r.Value = s Then
Set r1 = Range(r, r.Offset(0,8))
Set r2 = Workbooks("TMSPhaomReport.xls").Worksheet("TMSDL") .Range("A1")

r1.Copy r2
Exit sub
End If
Next

I still learning how to use this but the problem is when I run this Macro it
doesn't do anything. I'm guessing because r doesn't equal s but not really
sure. Can anyone help me out?

--
Message posted via http://www.officekb.com