Searching multiple worksheets and extracting data on new worksheet

[flowsnow06]

Hello,

Need some assistance with VBA. I have never tried with multiple worksheets as well as having duplicative values in worksheets.

What I need is to search across three worksheets in the workbook for a specific phrase or value and have it returned on another worksheet named Results.

There are 7 columns that have data in the worksheets that I would need to extract to the results worksheet.

I've done this before with VBA see below but never with multiple worksheets only with one. I tried adding the additional sheets (IGO & NIGO) in the sheets range but kept receiving an error. Any assistance you can provide would be very much appreciated.

Sub searchdata()

Dim erow as Long
Dim ws as Worksheet
Dim lastrow as Long
Dim count As Integer

lastrow=Sheets("Client").Cells(Rows.count,1).End(xlup).Row

For x=2 To lastrow
If Sheets("True")Cells(x,1)=Sheet1.Range("B2")Then
Sheet1.Range("A7")=Sheets("True").Cells(x,1)

I'm not sure if this will even be the appropriate code for my request, but again any assistance would be greatly appreciated.

 

[mumps]

Can the specific search value vary or is it the same all the time? Can that value be anywhere on the 3 sheets to be searched or is it in specific column? If in a specific column, what is the column letter? Do you want to return the entire row of the found search value to the "Results" sheet?

 

[flowsnow06]

Great questions and thanks for your help. The specific search value will vary as there may different contract numbers we need to search by. Those contract numbers can have multiple products associated with it which is why i indicated duplicative values. For instance if I did the search by contract number 7600 there are 3 products associated with this contract number so I would need the results page to pull all three when I perform the search. The value for the contract numbers will only be in column A. Yes I would need it to pull all the data in the rows as it would show me also who the distributor is. Any more questions please let me know.

 

[Fluff]

How about

Sub SearchCopy2Results()

   Dim ShtAry As Variant
   Dim Sht As Variant
   Dim Srch As String
   Dim Fnd As Range
   Dim Qty As Long
   Dim Cnt As Long
   
   ShtAry = Array("Clients", "IGO", "NIGO")
   Srch = InputBox("Input a search term")
   If Srch = "" Then Exit Sub
   For Each Sht In ShtAry
      With Sheets(Sht)
         Set Fnd = .Range("A1")
         Qty = WorksheetFunction.CountIf(.Columns(1), Srch)
         If Qty > 0 Then
            For Cnt = 1 To Qty
               Set Fnd = .Columns(1).Find(Srch, Fnd, , xlWhole, , , False, , False)
               Fnd.EntireRow.Copy Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Next Cnt
         End If
      End With
   Next Sht
End Sub

 

[flowsnow06]

Wonderful! That is pretty much what I needed. Sorry, but would also like to know is there a way that I can add the search to also include not just by contract number but also by Company name? Those values would be in column B. Also, is there anyway after a search if I go to search another client it clears the previous results?

 

[Fluff]

Try this

Sub SearchCopy2Results()

   Dim ShtAry As Variant
   Dim Sht As Variant
   Dim Srch As String
   Dim Fnd As Range
   Dim Qty As Long
   Dim Cnt As Long
   
   Sheets("Results").UsedRange.Clear
   ShtAry = Array("Clients", "IGO", "NIGO")
   
   Srch = InputBox("Input a search term")
   If Srch = "" Then Exit Sub
   For Each Sht In ShtAry
      With Sheets(Sht)
         Set Fnd = .Range("A1")
         Qty = WorksheetFunction.CountIf(.Range("A:B"), Srch)
         If Qty > 0 Then
            For Cnt = 1 To Qty
               Set Fnd = .Range("A:B").Find(Srch, Fnd, , xlWhole, , , False, , False)
               Fnd.EntireRow.Copy Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Next Cnt
         End If
      End With
   Next Sht
End Sub

 

[flowsnow06]

Thanks again. The only issue I am having with this now is that for some reason when I type in the name of the company name it is now only returning one result. The company may have multiple products and the company I searched with I know has more than one, but seems as though only the first product is coming up. If I do the search with the wildcard "*" at the end of the company name then all of the multiple products for that company return, but is there anyway to maybe build that in?

 

[Fluff]

Change xlWhole to xlPart

 

[flowsnow06]

Thanks I did try that, but it only works when entering the control number and not company name. Not sure if there is any other way if not I will just put a note on the document to include an *.

 

[Fluff]

Try

Sub SearchCopy2Results()

   Dim ShtAry As Variant
   Dim Sht As Variant
   Dim Srch As String
   Dim Fnd As Range
   Dim Qty As Long
   Dim Cnt As Long
   
   Sheets("Results").UsedRange.Clear
   ShtAry = Array("Clients", "IGO", "NIGO")
   
   Srch = InputBox("Input a search term")
   If Srch = "" Then Exit Sub
   For Each Sht In ShtAry
      With Sheets(Sht)
         Set Fnd = .Range("A1")
         Qty = WorksheetFunction.CountIf(.Range("A:B"), Srch & "*")
         If Qty > 0 Then
            For Cnt = 1 To Qty
               Set Fnd = .Range("A:B").Find(Srch, Fnd, , xlPart, , , False, , False)
               Fnd.EntireRow.Copy Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Next Cnt
         End If
      End With
   Next Sht
End Sub