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!
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!