VBA - Find in another Worksheet

[ElRugg]

Hi there,

I have a worksheet with a table in it (Sheet 1). I have another worksheet with the "legacy" data in it (Sheet 2) from before I created the new sheets. Because I added more information, pivot tables, and otherwise fleshed out the data in the new table, I couldn't just add the old without breaking some of the new formulas/etc.

Column A in both tables stayed the same though - a Name. I have conditional formatting that shows if the Name in Sheet 1 is the same as a name in Sheet 2. If so, I copy it, switch to Sheet 2, and do a basic find search.

I'm trying to do this through VBA instead to make it a bit easier.

Usually, I do this when a new name is added to the bottom of sheet 1. I have the below which helps with that:
Sheets("Sheet 2").Select
Cells.Find(What:=Sheets("Sheet 1").Range("A1").End(xlDown), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate

But usually is not always. I'm trying to figure out a way where the What to Find in Sheet 2 can be whichever cell I'm on in Sheet 1, not just the last one.

Thanks in advance!

 

[Fluff]

How about

Sub ELRugg()
   Dim Fnd As Range
   
   Set Fnd = Sheets("Sheet2").Range("A:A").Find(ActiveCell, , , xlPart, , , False, False)
   If Not Fnd Is Nothing Then Application.Goto Fnd, True
End Sub

 

[ElRugg]

Jeez, that was easy! I don't know why i thought I'd have to select the destination sheet first.

Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.