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

S

shizu

New Member
Joined
Mar 22, 2018
Messages
7
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Registration_No[/TD]
[TD]First_Name[/TD]
[TD]Other_Name[/TD]
[TD]Last_name[/TD]
[TD]1st_CA[/TD]
[TD]2nd_CA[/TD]
[TD]3rd_CA[/TD]
[TD]Exam[/TD]
[TD]Total[/TD]
[TD]Grade[/TD]
[TD]Position[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]9008[/TD]
[TD]Mike[/TD]
[TD]Joe[/TD]
[TD]Peter[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]56[/TD]
[TD]77[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]9009[/TD]
[TD]Luke[/TD]
[TD]Chan[/TD]
[TD]Lee[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]34[/TD]
[TD]46[/TD]
[TD]D[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9010[/TD]
[TD]Blake[/TD]
[TD]Harry[/TD]
[TD]Blake[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]69[/TD]
[TD]91[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9011[/TD]
[TD]Ugo[/TD]
[TD]Kelechi[/TD]
[TD]Amadi[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]45[/TD]
[TD]66[/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
drhatmrexcel said:
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
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
Another option.
Put this in the userform module
Code: 
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
 
Upvote 0
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
 
Upvote 0
My Aswer Is This said:
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
Click to expand...

Yes that's what I want but the new sheet would be like a template like below
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Micheal Joe Peters[/TD]
[TD]Attendance[/TD]
[TD]76 out of 82 days[/TD]
[TD]Total Average[/TD]
[TD]980[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Registration_No[/TD]
[TD]9008[/TD]
[TD]Class Position[/TD]
[TD]7th[/TD]
[TD]Next Term Opening date[/TD]
[TD]6th September 2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Subjects[/TD]
[TD]1st_CA[/TD]
[TD]2nd_CA[/TD]
[TD]3rd_CA[/TD]
[TD]Exam[/TD]
[TD]Total[/TD]
[TD]Position[/TD]
[TD]Grade[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Mathematics[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]50[/TD]
[TD]65[/TD]
[TD]12[/TD]
[TD]B[/TD]
[TD]45.8[/TD]
[/TR]
[TR]
[TD]English[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]31[/TD]
[TD]C[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]History[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]65[/TD]
[TD]80[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]66.5[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Extract multiple data from one sheet to another
Replies
3
Views
632
vishu
V
A
  • Question Question
Extract data from three sheets from several excel files
Replies
2
Views
466
abuzahira
A
muhammad susanto
  • Question Question
Extract Data Based On The Same of Header From Multiple Sheet Into One Sheet
Replies
9
Views
832
mumps
mumps
Nick30075
  • Question Question
Generate subset table from Master table
2 3 4
Replies
36
Views
4K
Peter_SSs
Peter_SSs
S
  • Question Question
How to extract new sheet of unique data from another sheet in a workbook using VBA Excel
Replies
4
Views
1K
s ganesh
S

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

Share this page

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