VBA - Find in another Worksheet

ElRugg

New Member
Joined
Jun 26, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 
Solution

ElRugg

New Member
Joined
Jun 26, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Jeez, that was easy! I don't know why i thought I'd have to select the destination sheet first.

Thanks so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,694
Members
415,921
Latest member
ExcelGeek5038

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top