HELP! VBA Find won't work when copied!

[Blackdeal]

I set up a spreadsheet with a find macro, works no problem. Now I am copying the macro to a new spreadsheet that is set up EXACTLY as the one it is coming from and I cannot get the macro to work. What happened? The text in red below is where the debugger keeps on highlighting.

Sub Find()
Dim sFind As String
Dim rSearch As Range
Dim cl As Range
sFind = Sheets("HDM Find").Range("A1")
With Sheets("EMLT Range Planner")
'set range to search, limit it used cells in range
Set rSearch = .Range(.Cells(1, 3), .Cells(.Rows.Count, 1).End(xlUp))
With rSearch
Set cl = .Find(What:=sFind, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cl Is Nothing Then '< find successful
cl.Offset(0, -1).Copy
Sheets("HDM Find").Range("A2").PasteSpecial
'<- change this to do something with the found cell
Else: MsgBox sFind & " not found"
End If
End With
End With
End Sub

 

[RoryA]

I thought you had removed the After:=activecell part?

 

[Blackdeal]

I had, but it was not working. When I closed the workbook, I did not save so it still had the original code.

 

[Blackdeal]

Ok, so I am a VBA rookie. Could anyone write some VBA code where a user enters text into ws2 cell A1, then clicks a button that runs a macro to find what they entered on ws1 column C, once found offset to column B, copy the cell and paste on ws2 A2?

That's what the code does on the original workbook, but is failing on the new workbook. I then have a bunch of vlookups on ws2 that populates a bunch of cells.

 

[Blackdeal]

Thanks for your help Nuked and RoryA, I took your suggestions and played around until I finally got it. In case you are interested, here is what I ended up with.

Sub Find()</SPAN>
    Dim sFind  As String</SPAN>
    Dim rSearch As Range</SPAN>
    Dim cl    As Range</SPAN>
    sFind = Sheets("HDM Find").Range("A1")</SPAN>
    Set rSearch = Sheets("EMLT Range Planner").Range("C5:C800")</SPAN>
        With rSearch</SPAN>
            Set cl = .Find(What:=sFind, LookIn:=xlFormulas, _</SPAN>
                           LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _</SPAN>
                           MatchCase:=False, SearchFormat:=False)</SPAN>
            If Not cl Is Nothing Then    '< find successful</SPAN>
                cl.Offset(0, -1).Copy</SPAN>
                Sheets("HDM Find").Range("A2").PasteSpecial</SPAN>
                '<- change this to do something with the found cell</SPAN>
            Else: MsgBox sFind & " not found"</SPAN>
            End If</SPAN>
        End With</SPAN>
End Sub

</SPAN></SPAN>