User form to query (up to) 8 worksheets

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
671
Office Version
365
Platform
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,723
Office Version
2013
Platform
Windows
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.
 

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
671
Office Version
365
Platform
Windows
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
 

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
671
Office Version
365
Platform
Windows
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
 

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
671
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
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.
 

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
671
Office Version
365
Platform
Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,723
Office Version
2013
Platform
Windows
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,688
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Cross-posted here.

Please note the forum rule on cross-posting, and follow it in the future. Thanks. :)
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,723
Office Version
2013
Platform
Windows
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?
 

Forum statistics

Threads
1,089,518
Messages
5,408,753
Members
403,224
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top