VBA to find duplicate cell from another sheet based row

I have ran into an issue with a spreadsheet and is needing a refresh due to new data sources. I have tried to put together some code but I am lost because the object needs to be able to select the row that it is sitting on. I have included a test file if it helps.

I am trying to come up with something that will
1 when button is clicked it will move to column Z on the row that it is sitting on
2 there will be a value there to remember
3 match that value to column A on sheet2, there will be a duplicate most likely
4 simply select (move excel view) the cell to the right of the duplicate- by one column

Seems easy writing it out but I am having a hard time getting 1 from the steps above to work so I haven't really been able to even begin trying to figure it out.

Any help would be greatly appreciated.