Hopefully an easy copy field, search different sheet, paste data

[koalakb]

Hi Everyone,
Excel 2013

I am hoping someone can help me, I am slowing trying to relearn excel, with the hopes of becoming an expert one day. Trying to learn coding is hard enough and searching for answers can often be more confusing than helpful, so I hoped to just try to explain what i am trying to do and see if you can assist with the code to figure out where it went wrong.

In my daily job, I have to look up a contact name of a person that is assigned to a certain building and then email them. The output sheet has the following columns "SWC CLLI" "Planner" "UID" and "Email Output"
The macro I setup has it so I paste the list of building codes, which can range anywhere from 1 to up to a few hundred. When we get into many sites is when it becomes to overbearing.

So basically I have 2 sheets within a workbook, the sheet with a full list of buildings within the state, contact names and UIDs I create from a data dump, the second sheet is just a sheet to paste all the building codes an hopefully get the info i need extracted from. Please note, that eventually more sheets will get added with data for all the states i cover, but for now, I just have IL as the starting point. Sheets for MO, TX, CA etc will be added

Here is the code:

Sub test()
'
' test Macro
' test lookup
'
' Keyboard Shortcut: Ctrl+w
'
Range("A4").Select
Selection.Copy
Sheets("IL Planning").Select
Cells.Find(What:="PRRGILXL", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("D226").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Output").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "MICHAEL KEHL(mk9813),"
Range("C4").Select
ActiveSheet.Paste
Range("E4").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "mk9813;"
Range("E5").Select
End Sub

The problem is, is i have it set for the 1st entry, in this case, "PRRGILXL" but the macro seems to always look for that specific data. Is there a way to set it so it grabs the data in column A, row 2 and searches against the building list. Copy that data back to my output sheet and breakdown the information as seen below?

Column A (SWC CLLI) = the building code pasted from another spreadsheet
Column B (Planner) - Copies the data as it shows on the building list spreadsheet and formats the paste to PasteValuesAndNumberFormats format. It basically comes through as a hyperlink, I want to change it to text so we can extract the UID from it.
This is what comes over MICHAEL KEHL(mk9813),
Column C "UID" - the 6 character tag for the person assigned to it that we extracted from, the data between the () so in this case, mk9813
Column E "Email Output" - Same information as Column C but adds a ";" to the end so it can be pasted into outlook. So it modifies to show mk9813;
[TABLE="width: 877"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD][/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]SWC CLLI[/TD]
[TD]Planner[/TD]
[TD]UID[/TD]
[TD][/TD]
[TD]Email Output[/TD]
[/TR]
[TR]
[TD]PRRGILXL[/TD]
[TD]MICHAEL KEHL(mk9813),[/TD]
[TD]mk9813[/TD]
[TD][/TD]
[TD]mk9813;[/TD]
[/TR]
[TR]
[TD]PRRGILXL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DSPLILXL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WLNGILWG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WKGNILWK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WLNGILWG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LBVLILLI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LKVLILLK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NPVLILNA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Things I need clarification/help on:
1) When i run the macro, for this i asigned CTRL W, the macro coding has the fixed data within it. I want it to grab whatever data is there when you do a CTRL-C or Copy
2) Since the list in column A can vary from 1 location to 100+, is there a way to make it go through the whole list and stop when it hits a blank? so we would only have to run the macro once, it would go through the data in Column A and stop when it gets to a blank or the bottom of the list, in this case, the value "NPVLILNA"
3) If it encounters one it cannot match against the building info list, it either populates or gives a popup of a message like "Possible OOF or Incorrect WC"

Is there a way I can post the files up for you to review? Please let me know, any assistance you can provide is greatly appreciated.

Thank you everyone, hopefully i didnt confuse you. I know its a simple macro, but I was trying to do my best to explain since i cant upload docs