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

kdlenort

Board Regular
Joined
May 17, 2010
Messages
102
Office Version
  1. 2013
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
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:p></o:p>
<o:p></o:p>
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" o:preferrelative="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><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>:confused:<o:p></o:p>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Have you looked at AdvanceFilter or even MSQuery? Both are much more suited for this!!

lenze
 
Upvote 0
Thanks for your quick response lenze!:)

I did try MSQuery but because I know so little about it I couldn't get it to do want I wanted.

I would like to use a code so that it will be very easy for others in our office to use the Search page.
 
Upvote 0
Yes, I believe so. Column F is the column that contains the names that will be searched for from the Search sheet.

I have no control over what is in the columns on the ??? Register sheets so I must try to work with it.;)
 
Upvote 0
A few more questions!!
1) Will the name be in more that one sheet??
2) If so, do you need to identify the sheet with the data?
3) How many register sheets are there?

lenze
 
Upvote 0
Yes, the name will possibly be in more than one sheet.

Yes, I think bringing up the sheetname of the name being searched for would be a benefit.;)

The number of register sheets may vary from 10 to 20 but for the most part there will be 12 Register sheets.:)
 
Upvote 0
This should get you started
Assumptions
1) The Name is entered in $B$2 on the "Search" sheet
2) On the "Search" sheet, A5:H5 contain Headers (H5 is the "Source Sheet")
3) The name only appears once in each sheet (this can be changed if needed)
4) You don't want to retain previous retrievals
5) You don't need to retrieve the Name (Column "F" on source sheet)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Dim ws As Worksheet
Dim sh As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Dim C As Range
Dim D As Range
Set sh = Sheets("Search")
Dim Flag As Boolean
Flag = False
Set D = sh.Range("$A$6:$H" & Cells(Rows.Count, "H").End(xlUp).Row + 1)
D.ClearContents
For Each ws In Worksheets
If ws.Name <> "Search" Then
  LR1 = ws.Cells(Rows.Count, "F").End(xlUp).Row
  With ws.Range("$F$2:$F" & LR1)
    Set C = .Find(Target, LookIn:=xlValues)
    If Not C Is Nothing Then
       LR2 = sh.Cells(Rows.Count, "A").End(xlUp).Row
       ws.Cells(C.Row, 2).Resize(1, 4).Copy sh.Cells(LR2 + 1, "A")
       ws.Cells(C.Row, 7).Resize(1, 3).Copy sh.Cells(LR2 + 1, 5)
       sh.Cells(LR2 + 1, 8) = ws.Name
       Flag = True
    End If
  End With
End If
Next ws
If Flag = False Then MsgBox "Name Not Found"
End Sub
HTH
lenze
 
Upvote 0
Thank you for the code lenze.:cool:

Is there a way to bring the information up on the Search sheet if the name appears more than once on a register sheet? ;) We may have the name in the Register 3 or 4 times.

I tried altering the code with no luck. This is the first time I've seen these particular pieces of code and I do not know what they are doing.:(
 
Upvote 0
I anticipated that :biggrin:. We can add a FindNext
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Dim ws As Worksheet
Dim sh As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Dim C As Range
Dim D As Range
Set sh = Sheets("Search")
Dim Flag As Boolean
Flag = False
Set D = sh.Range("$A$6:$H" & Cells(Rows.Count, "H").End(xlUp).Row + 1)
D.ClearContents
For Each ws In Worksheets
If ws.Name <> "Search" Then
  LR1 = ws.Cells(Rows.Count, "F").End(xlUp).Row
  With ws.Range("$F$2:$F" & LR1)
    Set C = .Find(Target, LookIn:=xlValues)
    If Not C Is Nothing Then
       fstAdd = C.Address
       Do
       LR2 = sh.Cells(Rows.Count, "A").End(xlUp).Row
       ws.Cells(C.Row, 2).Resize(1, 4).Copy sh.Cells(LR2 + 1, "A")
       ws.Cells(C.Row, 7).Resize(1, 3).Copy sh.Cells(LR2 + 1, 5)
       sh.Cells(LR2 + 1, 8) = ws.Name
       Set C = .FindNext(C)
       Loop While Not C Is Nothing And fstAdd <> C.Address
       Flag = True
    End If
 End With
End If
Next ws
If Flag = False Then MsgBox "Name Not Found"
End Sub
HTH
lenze
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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
Back
Top