Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display results from search on one worksheet (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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.

  3. #3
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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?
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    See if this does what you want:

    <code>
    Public Sub PullAvail()
    Dim oWS As Worksheet
    Dim I As Long, J As Long
    Worksheets("Results").Range("A2:IV65536").ClearCon tents
    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").Off set(J, 0)
    J = J + 1
    End If
    Next I
    End If
    Application.CutCopyMode = xlCopy
    Next oWS
    End Sub
    </code>
    Legare Coleman

  9. #9
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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.
    Legare Coleman

  12. #12
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display results from search on one worksheet (Excel 2003)

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

  13. #13
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    Try

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

  15. #15
    Lounger
    Join Date
    Jul 2006
    Location
    N. Highlands, California, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display results from search on one worksheet (Excel 2003)

    You are a life saver! Thank you.

    SME

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •