Vba and userform for Extracting data from multiple sheets and into a table in a new sheet and then printed

[shizu]

Registration_No	First_Name	Other_Name	Last_name	1st_CA	2nd_CA	3rd_CA	Exam	Total	Grade	Position	Average
9008	Mike	Joe	Peter	6	7	8	56	77	A	2	70
9009	Luke	Chan	Lee	2	4	6	34	46	D	4
9010	Blake	Harry	Blake	8	8	6	69	91	A	1
9011	Ugo	Kelechi	Amadi	6	8	7	45	66	B	2

<tbody>
</tbody>

I have between 10 to 12 sheets in a workbook containing students' results in the above format, each sheet is for a subject (For example Mathematics, English, Physics, Chemistry, Biology etc) and column 'exam' to 'Average' contains formulas.

Now I want to extract each student result (using the data from the column 'Registration_No' as the unique identifier) from all the sheets in the workbook into a new sheet that I can then print. I want a user-form which would serve like a search bar in which the 'Registration_no@ would be typed in and the result generated then the user can then print it.

Please who got ideas to help me out.

 

[drhatmrexcel]

Try this simple spreadsheet that just uses a lookup an needs no vba to accomplish
https://www.dropbox.com/s/ymg6b42i8x4yegi/Sample.xlsx?dl=0

 

[shizu]

Try this simple spreadsheet that just uses a lookup an needs no vba to accomplish
https://www.dropbox.com/s/ymg6b42i8x4yegi/Sample.xlsx?dl=0

Thanks for the effort, but it is not exactly what I was looking for, I want a situation whereby a third party can input the 'Registration_No' into a user-form and then click ok and the student's result would then be extracted into a new sheet for printing. This is to enable the user to be able to print each student result by inputting their Registration_No into the userform.

If this can't be possible with Excel any idea on other possible solution on how to extract each students result from all the sheets into a new sheet for printing

 

[drhatmrexcel]

OK have the user form input the registration number and then the result would be pasted into the lookup cell of the results sheet, unhide the results sheet, switch to the results sheet which has the lookups in and make it the active sheet, and then print the results sheet.

 

[Fluff]

Another option.
Put this in the userform module

Option Explicit
Dim Dic As Object

Private Sub ComboBox1_Change()
   Dim Ws As Worksheet
   
   Sheets("print").UsedRange.Offset(1).Clear
   For Each Ws In Worksheets
      If Not Ws.Name = "Print" Then
         If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
         Ws.Range("A1").AutoFilter 1, Me.ComboBox1.Value
         Ws.AutoFilter.Range.Offset(1).SpecialCells(xlVisible).Copy Sheets("Print").Range("A" & Rows.Count).End(xlUp).Offset(1)
         Ws.AutoFilterMode = False
      End If
   Next Ws
   Sheets("Print").PrintOut
End Sub

Private Sub UserForm_Initialize()
   Dim Ws As Worksheet
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   Sheets("Print").UsedRange.Offset(1).Clear
   For Each Ws In Worksheets
      For Each Cl In Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
         If Not Dic.exists(Cl.Value) And Not IsEmpty(Cl) Then Dic.Add Cl.Value, Nothing
      Next Cl
   Next Ws
   Me.ComboBox1.List = Dic.keys
End Sub

 

[My Aswer Is This]

So are you saying:
You enter 9010 into Userform Textbox1
Then you want script to search all sheets for the number 9010 in column A
If 9010 is found in Column A then copy that entire row into a new sheet named 9010

And do this on all sheets in the workbook

If this is not what you want then provide me with more details

 

[shizu]

So are you saying:
You enter 9010 into Userform Textbox1
Then you want script to search all sheets for the number 9010 in column A
If 9010 is found in Column A then copy that entire row into a new sheet named 9010

And do this on all sheets in the workbook

If this is not what you want then provide me with more details

Yes that's what I want but the new sheet would be like a template like below

Name	Micheal Joe Peters	Attendance	76 out of 82 days	Total Average	980
Registration_No	9008	Class Position	7th	Next Term Opening date	6th September 2018
Subjects	1st_CA	2nd_CA	3rd_CA	Exam	Total	Position	Grade	Average
Mathematics	5	5	5	50	65	12	B	45.8
English	4	5	1	40	50	31	C	65
History	3	6	6	65	80	1	A	66.5

<tbody>
</tbody>