Code to bring up info if name is entered on a separate sheet

[kdlenort]

I want a code to run some type of a lookup which would bring up all the information, specific cells within the row of the name I type in to a sheet named "Search".<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Looking in several sheets named "??? Register" (the 3 question marks will be different letters or numbers on each Register sheet) starting in row 137.<o></o>
<o></o>
If the name does exist I want the information from each time the name is found, from columns B, C, D, E, G, H, & I to be listed on the Search sheet.<o></o>
If the name doesn't exist on the ??? Register sheets I would like a pop up to open and indicates "Name not found" or "Name doesn't exist".<o></o>
<o></o>
Is this possible??<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><o></o>

 

[lenze]

I corrected this line

Set D = sh.Range("$A$6:$I" & Cells(Rows.Count, "I").End(xlUp).Row + 1)

to

Set D = sh.Range("$A$6:$H" & Cells(Rows.Count, "H").End(xlUp).Row + 1)

The MsgBox line for Row number was for testing. You can remove it!!

This code DOES work!! Here is what it does!!
1. When a Name is selected in B2 on Sheet "Search", it looks for that name in Column "F" on Sheet "SS".
2. If found, it copies Columns "B:E" and "G:H" from Sheet "SS" into Columns "A:G" on sheet "Search". If then places the value in Sheet "SS", Column "J" in Sheet "Search" , Column "H"
3) The above is repeated until the name is no longer found in Column "F" Sheet "SS"
4) If the name is not found at all, the "Name Not Found" message appears

lenze

 

[kdlenort]

Thanks lenze you are wonderful

Code is working great.

Is there a setting I can use so that I can protect this sheet and not cause the code to want to debug?

Thanks for all you've been doing for me, I really appreciate it.

 

[kdlenort]

I figured out how to hide the cells on the Search sheet so I can protect it.

Thanks