copying entire row from one sheet to another

hey all....

i've managed to find this super codes what someone wrote along time ago(sometime in 2005)...a whole part of it helped me in whatever i'm trying to achieve...but there's still some minor problems that i've encountered..

this is the original code

Sub FindMe()
    Dim intS As Integer
    Dim rngC As Range
    Dim strToFind As String, FirstAddress As String
    Dim wSht As Worksheet
    Dim rngSearch As Range
    
    Application.ScreenUpdating = False
    
    intS = 1
    Set rngSearch = Worksheets("Sheet1").Range("A1:E2000")
    Set wSht = Worksheets("Sheet2")
    
    strToFind = InputBox("Enter the title to find")
    
    Do While strToFind <> ""
        With rngSearch
            Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
            If Not rngC Is Nothing Then
                FirstAddress = rngC.Address
                Do
                    rngC.EntireRow.Copy wSht.Cells(intS, 1)
                    intS = intS + 1
                    Set rngC = .FindNext(rngC)
                Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
            End If
        End With

        strToFind = InputBox("Enter the title to find")

    Loop

    Application.ScreenUpdating = True
    Set rngSearch = Nothing
    Set wSht = Nothing
    Set rngC = Nothing
End Sub

this code helps you to search a word/phrase in sheet1 and copy the whole row, then pasting it onto a new sheet....

my problem is, in my case i have several sheets to combine with....whereas this one only identify 1 particular sheet (see bolded section in the code)
can anyone out there help me with this ?

appreciate your help...
cheers

Hi,

What about multifind ...from Ron

Sub MultiFind() 
Worksheets.Select 
Application.Dialogs(xlDialogFormulaFind).Show 
ActiveSheet.Select 
End Sub

HTH
Carim

Originally Posted by Carim

Hi,

What about multifind ...from Ron

Sub MultiFind() 
Worksheets.Select 
Application.Dialogs(xlDialogFormulaFind).Show 
ActiveSheet.Select 
End Sub

HTH
Carim

hye carim...
not to sound like such a noob(which i'm unfortunately am )...where should i put that in ?

i do have a command button code that i need to fill in
eg.

Private Sub CommandButton1_Click()

FindMe

End Sub

mfg..
eddy

dear davesexcel...

sorry but that didn't bring much help at all...
i'm not sure whether or not you guys understood what i actually wanted from the original code above....

Originally Posted by EddyAddelan

my problem is, in my case i have several sheets to combine with....whereas this one only identify 1 particular sheet (see bolded section in the code)
can anyone out there help me with this ?

appreciate your help...
cheers

The code may be extended to search all sheets (except the results sheet) by looping through the Worksheets Collection:

For Each ws in Workbook.Worksheets
search, copy and paste
Next ws

Sub WorksheetSearch()
    Dim lngRow As Long
    Dim rngC As Range
    Dim strToFind As String, FirstAddress As String
    Dim wsResults As Worksheet
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    lngRow = 1                          'results row
    Set wsResults = Worksheets("Sheet3")     'results sheet
    strToFind = InputBox("Enter the title to find")
    
    Do While strToFind <> ""
        For Each ws In ThisWorkbook.Worksheets
            If ws.Index <> wsResults.Index Then      'don't search the results sheet
                With ws.Range("A1:E2000")   'search this range on each sheet
                    Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
                    If Not rngC Is Nothing Then
                        FirstAddress = rngC.Address
                        Do
                            rngC.EntireRow.Copy wsResults.Cells(lngRow, 1)
                            lngRow = lngRow + 1
                            Set rngC = .FindNext(rngC)
                        Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
                    End If
                End With
            End If
        Next

        strToFind = InputBox("Enter the title to find")
    Loop

    Application.ScreenUpdating = True
    Set wsResults = Nothing
    Set rngC = Nothing
End Sub

Hi,
The following code was written to search 12 sheets and put results in a sheet called "Summary Sheet"

Sub test2()
Dim SHTNM As String
Dim R As Long
Dim Z As String
Application.ScreenUpdating = False

R = 2 ' Summary Row to start paste of InProd rows from other sheets
Sheets("Source").Range("A1").Value = InputBox("What do you like to search")

Z = Sheets("Source").Range("A1").Value


For sht = 1 To 12 ' sheets ( 1 to 12) production sheets
SHTNM = "ProdLine" & sht
Sheets(SHTNM).Select
With Sheets(SHTNM).Range("A:A", Range("A100").End(xlUp).Address)
Set c = .Find(Z, LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
Do
Sheets(SHTNM).Range(c.Address).EntireRow.Copy
Sheets("Summary").Cells(R, 1).Insert Shift:=xlDown
R = R + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


Next sht
Sheets("Summary").Select
End Sub


this might help you.
Jaz

dear jaz and T-J...
thanks alot for ur help, the guides that you guys gave really helped me alot..
another thing though, both of you assigned for the search function to search for ALL the sheets EXCEPT the summary/result sheet...the thing is, in my workbook, there so many other sheets that i don't want to be included...
can't i just assign the names of the sheets that needed to be included ? if so, how am i to do it ?

cheers
eddy

If using the Worksheets Collection, use a Select Case to check the worksheet name:

For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case "Sheet1", "Sheet3", "Sheet4", "Sheet5"    'sheets to search
            Set rngSearch = ws.Range("A1:E2000")
            Set wSht = Worksheets("Sheet2")             'results sheet
            
            With rngSearch
              'search code here
            End With

    End Select
Next

Originally Posted by T-J

If using the Worksheets Collection, use a Select Case to check the worksheet name:

For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case "Sheet1", "Sheet3", "Sheet4", "Sheet5"    'sheets to search
            Set rngSearch = ws.Range("A1:E2000")
            Set wSht = Worksheets("Sheet2")             'results sheet
            
            With rngSearch
              'search code here
            End With

    End Select
Next

hey T-J,
the code that u gave me works fine, but now a new problem has raised...
the problem by your code is that, it pastes the info from sheet1, but then it pastes the new info from sheet2 OVER infos from sheet1...

tried manupilating it myself, but the inputbox keeps coming out, and i have to enter what i wanted to find twice...

here's the list of my actual VBA codes that i have...

Private Sub CommandButton1_Click()

FindMe

End Sub

Private Sub CommandButton2_Click()
    
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("A1").Select
    
End Sub


Sub FindMe()
    Dim intS As Integer
    Dim rngC As Range
    Dim strToFind As String, FirstAddress As String
    Dim wSht As Worksheet
    Dim rngSearch As Range
    
    Application.ScreenUpdating = False
    
    intS = 2
    Set rngSearch = Worksheets("Tabelle1").Range("A1:IV65535")
    Set wSht = Worksheets("Tabelle3")
    
    strToFind = InputBox("Geben Sie den PLZ oder NE Kennung")
    
    Do While strToFind <> ""
        With rngSearch
            Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
            If Not rngC Is Nothing Then
                FirstAddress = rngC.Address
                Do
                    rngC.EntireRow.Copy wSht.Cells(intS, 1)
                    intS = intS + 1
                    Set rngC = .FindNext(rngC)
                Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
            End If
        End With

        strToFind = InputBox("Klicken Sie bitte Abbrechen")

    Loop

    Application.ScreenUpdating = True
    Set rngSearch = Nothing
    Set wSht = Nothing
    Set rngC = Nothing
    
    
    Set rngSearch = Worksheets("Tabelle2").Range("A1:IV65535")
    Set wSht = Worksheets("Tabelle3")
    
    strToFind = InputBox("Geben Sie den PLZ oder NE Kennung")
    
    Do While strToFind <> ""
        With rngSearch
            Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
            If Not rngC Is Nothing Then
                FirstAddress = rngC.Address
                Do
                    rngC.EntireRow.Copy wSht.Cells(intS, 1)
                    intS = intS + 1
                    Set rngC = .FindNext(rngC)
                Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
            End If
        End With

        strToFind = InputBox("Klicken Sie bitte Abbrechen")

    Loop

    Application.ScreenUpdating = True
    Set rngSearch = Nothing
    Set wSht = Nothing
    Set rngC = Nothing
End Sub

because i don't actually know how to do it, i HAD to re-write the codes again(the search-copy-paste function)i know there's an easier way....just can't get to it

cheers
eddy