Search and copy problem

Herman

New Member
Joined
Feb 28, 2002
Messages
28
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You could try a procedure along the following lines:

Code:
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-07 09:11
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top