User form to query (up to) 8 worksheets

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
1,023
Office Version
  1. 365
Platform
  1. Windows
I have a workbook containing 8 identical sets of data, all laid out with the same persons occupying the same row and the same data occupying the same columns.

My question is, is it possible for a userform to ‘pull’ data from all 8 sheets? What I’m trying to achieve is to search on one name and the userform to then populate with all 8 sets of data in respect of that person.

Thanks for your help.

Mel
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
This can surely be done but we need more specific details.

Like you want to search for same value in all 8 sheets.
You said:
What I’m trying to achieve is to search on one name

OK your going to choose a name from a Userform listbox and then search column G of all 8 sheets for that name and then copy that row to a new sheet named "Search"

Or something like that.

Please give specific details.
 
Upvote 0
Thanks for your reply. I've tried to upload a sample workbook to better explain what I'm hoping to achieve but without success! In my workbook, all sheets are set out identically and are populated automatically through links. Each sheet contains details of each players' statistics i.e. matches played, runs scored, overs bowled, wickets taken etc. Each sheet represents a different type of match/competition.

What I'd like my UserForm to be able to do is to select, say Player A, and pull in all the stats from each sheet. Line 1 on the User Form competition A, line 2 competition B and so on. If I could upload my sample workbook it might be a bit clearer, too!

Mel
 
Upvote 0
I've added the XL2BB add in but when i've pasted the ranges all that appears here is coding, which seems less than helpful...

Mel
 
Upvote 0
Does this help? This is a sample of what is on each sheet and what I'm trying to achieve is a UserForm to collate data from all sheets.

Mel

NameMatchesRunsHighest ScoreRuns/per 100 ballsAverageNot Out100's
Player 1
Player 2
Player 3
 
Upvote 0
I've added the XL2BB add in but when i've pasted the ranges all that appears here is coding, which seems less than helpful...

Mel
If you mean something like
1582465808770.png

That's perfectly normal & will be converted into a table when you hit the reply button.
 
Upvote 0
I've made, i think a minuscule amount of progress, although I'm still seriously struggling. Here is a copy of my user form:
1582536769882.png
but I can't get the code to work. Here's what I've tried so far but without success:

Private Sub CommandButton8_Click()

Dim RecordRow As Long
Dim RecordRange As Range

' Turn off default error handling so Excel does not display
' an error if the record number is not found
On Error Resume Next

' Find the row in the table that the record is in
RecordRow = Application.Match(CLng(TextBox184.Value), Range("Table4[Record]"), 0)

' Set RecordRange to the first cell in the found record
Set RecordRange = Range("Table4").Cells(1, 1).Offset(RecordRow - 1, 0)

' If an erro has occured i.e the record number was not found
If Err.Number <> 0 Then

ErrorLabel.Visible = True
On Error GoTo 0
Exit Sub

End If

' Turn default error handling back on (Let Excel handle errors from now on)
On Error GoTo 0

' If the code gets to here the record number was found
' Hide the error message 'Not Found'
ErrorLabel.Visible = False
' and populate the form fields with the record's data
TextBox66.Value = RecordRange(1, 1).Offset(0, 1).Value
TextBox67.Value = RecordRange(1, 1).Offset(0, 2).Value
TextBox68.Value = RecordRange(1, 1).Offset(0, 3).Value
TextBox69.Value = RecordRange(1, 1).Offset(0, 4).Value

End Sub

The first four worksheets have been 'converted' into tables "sheet 1 = table 4, 2 = table 3, 3 = table 2 and 4 = table 1. Show form, print and close all work but that's it! As you can see, I've tried the first four text boxes...

Your kind help will be invaluable!

Mel
 
Upvote 0
You said:
Show form, print and close all work but that's it! As you can see, I've tried the first four text boxes...

I see no code here that shows the userform or does any printing.
 
Upvote 0
Cross-posted here.

Please note the forum rule on cross-posting, and follow it in the future. Thanks. :)
 
Upvote 0
In the Textbox shown at top left corner of you screen your going to enter a players name.
And search each sheet for that name and then get the values from that sheet and enter into your Textboxes is that true?

What is the name of that textbox?
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

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