Search and copy problem

[Herman]

I have a question.

i have 3 sheets, i like to search for a value for example 3 in the first 2 sheets
and if it is found in sheetsthen copy the row from the sheet and paste it in sheet3, the same for sheet2 if it is also found there then copy it in sheet3.
number of sheets is not always the same so i thought for each ws , but i get lost after it.

anyone knows how to search across multiple sheets and paste it in 1 sheet.

[zacemmel]

I have the answer to your question, but I'm not going to give it to you.

[Herman]

Why not, if i have done something wrong please tell me so i won't do it again.
i am not aware of doing anything wrong here..
i thought this board was about helping other people.

greetings herman

[Asala42]

Heya Herm,

I honestly have no clue whether Zac was joking or was legitimately irked at something - I'm guessing he was playing with you unless there's another thread I missed. Moving onward:

Are you looking specifically for a VBA solution or perhaps something with formulas. I'm still on the beginner side of the VBA pool, but for formula's I'm guessing a series of VLOOKUPs will do the trick.

Can you give out more of the specifics of what you've got in front of you? If a formula approach is ok I'm guessing you'll want either a VLOOKUP/IF or VLOOKUP/INDIRECT combo depending on how many sheets your dealing with.

Hope that helps,
Adam

[zacemmel]

Nothing personal Herman; I was just kidding around

[Herman]

A vlookup is working fine on 1 sheet, but i want to search on all sheets and copy multiple found rows to another blank sheet and that isn;t working yet.

Herman

[zacemmel]

Vlookup doesn't work with 3D references. I would suggest using VBA.

[Asala42]

How many sheets do you need to search through?

More = More likely you want a VBA solution.

I take it these sheets are set up symetric to each other? If so what ranges (for future example's sake)

Adam

[Herman]

I have to look in aprox. 10 sheets.
sheets are not al the same, the column where the value stands i am looking for is always column b.
searching for value 1224 in 10 sheets could result in 4 found(sheet1,sheet5,sheet6,sheet7)
i want to have all the found rows in the sheets in one new worksheet.

[NateO]

You could try a procedure along the following lines:

Sub Search_Entire_Workbook()
Dim n As Integer, sht As Worksheet, act As Range, act2 As Range, tmpact As Range
Dim o As Long
myVar = InputBox("Please Enter Search Term")
Application.ScreenUpdating = False
n = Sheets.Count
o = 2
Sheets.Add After:=Sheets(n)
On Error GoTo errorhandler
Sheets(n + 1).Name = "SearchResults"
Sheets("SearchResults").[a1] = "Results:"
n = Sheets.Count
For Each sht In ThisWorkbook.Worksheets
    If sht.Index = n Then Exit Sub
    Set act = sht.Cells.Find(What:=myVar)
    If Not act Is Nothing Then
    act.EntireRow.Copy Sheets(n).Range("a" & o)
    Sheets(n).Range("a" & o).AddComment Text:="result location: " & _
        sht.Name & "!" & act.Address
    Set tmpact = act
again:
    Set act2 = sht.Cells.Find(What:=myVar, After:=tmpact)
    If Not act2 Is Nothing Then
        If act2.Address = act.Address Then
            Else:
            If o < 65536 Then
            o = o + 1
            act2.EntireRow.Copy Sheets(n).Range("a" & o)
            Sheets(n).Range("a" & o).AddComment Text:="result location: " & _
                sht.Name & "!" & act2.Address
            Set tmpact = act2
            GoTo again
            Else: MsgBox "Your search has returned too many rows to process."
            Exit Sub
            End If
        End If
    End If
    End If
    If o < 65536 Then
    o = o + 1
    Else: MsgBox "Your search has returned too many rows to process."
    Exit Sub
    End If
Next sht
Application.ScreenUpdating = True
Exit Sub
errorhandler:
Application.ScreenUpdating = True
MsgBox ("You need to delete the results sheet before running this procedure." _
    & Chr(13) & Chr(13) & "Delete the sheet ""SearchResults,"" then run again.")
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True
End Sub

Place it in a normal module and give her a whirl. She's built to find your value no matter what column, no matter what type of searcheable material, no matter which row has blank cells, etc..... Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-07 09:11 ]