Display results from search on one worksheet (Excel 2003)

Topic

New Reply

Is there a way to search within multiple worksheets and display the results on another worksheet? For example: I have over ten worksheets, each with different job positions that we have available. This information is broken down further by department. So, if someone needs to know which positions we have open, I would like only the jobs that we have open to be displayed in one location on a blank worksheet. Make sense?

Thanks,

SME

Reply | Quote

Replies

You could reorganize the data to be in one long table on one worksheet. You could then use the built-in AutoFilter or Advanced Filter to show only open positions.
You could still use other worksheets linking to the “big” sheet for reporting purposes.

Or you could write a macro that loops through the worksheets and copies information to a new worksheet. If you need help with that, we’d have to know the layout of the worksheets.

Reply | Quote

Thank you for such a quick response Hans. You always seem to be around when I need something. I have attached an Excel spread sheet in the same basic layout as the one I am currently working on. Would you please assist me with writing a macro to pull the data for a new worksheet?

Thanks,
SME

Reply | Quote

How can you tell if a position is “open” or not? Are there any sheets in the workbook other than the RESULTS worksheet and the department worksheets? If so, how can the code tell which sheets to pull data from?

Reply | Quote

If the position is open it would say “Position open” instead of an employees name. The only worksheets are department worksheets. There are about 15 worksheets, set up in the same manner as the accounting and development worksheets in the example attachment. I’m not sure how the code would know to pull data from the work sheets. I am very new to Excel.

SME

Reply | Quote

I agree with Hans’ suggestion and here is a sample. There is in general no need to create multiple identically formatted sheets. Creating 1 large one allows using builtin features…

You can filter on “program” to display from this 1 sheet what you created multiple sheets for.

You can filter on “Employee name” to get the “?” (or “Position open”)

Steve

Reply | Quote

Try this:

– Create a worksheet named Open.
– Copy the first row of any of the department sheets to Open.
– Use the following macro:

Sub ListOpen()
Dim wsh As Worksheet
Dim wshOpen As Worksheet
Dim r As Long
Dim n As Long
Dim t As Long

On Error GoTo ErrHandler

Application.ScreenUpdating = False
Set wshOpen = Worksheets(“Open”)
wshOpen.Range(“A2:F65536”).ClearContents
t = 1
For Each wsh In Worksheets
If Not wsh.Name = “Open” Then
n = wsh.Range(“A65536”).End(xlUp).Row
For r = 2 To n
If LCase(wsh.Range(“E” & r)) = “position open” Then
t = t + 1
wsh.Range(“A” & r & “:F” & r).Copy _
Destination:=wshOpen.Range(“A” & t)
End If
Next r
End If
Next wsh

ExitHandler:
Set wsh = Nothing
Set wshOpen = Nothing
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

– Warning: the macro clears any existing data from the Open sheet.

Reply | Quote

Good Morning,

Is there a way to change this macro, so it can search and return more than one item?

Sub ListOpen()
Dim wsh As Worksheet
Dim wshOpen As Worksheet
Dim r As Long
Dim n As Long
Dim t As Long

On Error GoTo ErrHandler

Application.ScreenUpdating = False
Set wshOpen = Worksheets(“Open”)
wshOpen.Range(“A2:F65536”).ClearContents
t = 1
For Each wsh In Worksheets
If Not wsh.Name = “Open” Then
n = wsh.Range(“A65536”).End(xlUp).Row
For r = 2 To n
If LCase(wsh.Range(“E” & r)) = “position open” Then
t = t + 1
wsh.Range(“A” & r & “:F” & r).Copy _
Destination:=wshOpen.Range(“A” & t)
End If
Next r
End If
Next wsh

ExitHandler:
Set wsh = Nothing
Set wshOpen = Nothing
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

For example instead of just “If LCase(wsh.Range(“E” & r)) = “position open” Then”, could I have it search for “position open” and “temp assignment”? If so, can you please tell me how to change the macro?

Thank you!

SME

Reply | Quote

Try

If LCase(wsh.Range(“E” & r)) = “position open” Or LCase(wsh.Range(“E” & r)) = “temp assignment” Then

Reply | Quote

You are a life saver! Thank you.

SME

Reply | Quote

See if this does what you want:


Public Sub PullAvail()
Dim oWS As Worksheet
Dim I As Long, J As Long
Worksheets("Results").Range("A2:IV65536").ClearContents
J = 1
For Each oWS In Worksheets
If oWS.Name "Results" Then
For I = 1 To oWS.Range("A65536").End(xlUp).Row - 1
If oWS.Range("E1").Offset(I, 0).Value = "Position open" Then
oWS.Range("A1").Offset(I, 0).EntireRow.Copy
Worksheets("Results").Paste Destination:=Worksheets("Results").Range("A1").Offset(J, 0)
J = J + 1
End If
Next I
End If
Application.CutCopyMode = xlCopy
Next oWS
End Sub


Reply | Quote

Thank you both for all of your help and patience today. Just a few more questions if I may. How can I set the range of the macro? Legare, it gives me an error message “Subscript out of range”. Hans your macro pulls the positions that are open and beautifully sorts them, but how can I tailor the macro to pull other information such as “Specialist” instead of “Positions open”? Could I copy your macro and change it to pull that data, and if so what information would I change?

Thank you,

SME

Reply | Quote

The line that selects records is

If LCase(wsh.Range(“E” & r)) = “position open” Then

It looks in column E for the text “position open”. If you want to look in column B for the exact text “development specialist”, use

If LCase(wsh.Range(“B” & r)) = “development specialist” Then

If you want to look for values in column B that contain the word “specialist”, use

If LCase(wsh.Range(“B” & r)) Like “*specialist*” Then

Reply | Quote

My macro will result in the error if you do not have a worksheet in the workbook named Results like you had in the workbook you uploaded. The macro can add that worksheet if you want.

Reply | Quote

Thank you Legare for clarify. You have been a very big help.

Reply | Quote