Finding a Search String in Multiple Sheets and copying Rows to a new sheet

[amnya]

Gents,
I have an excel sheet with multiple sheets, i want a vb script which enables me to copy the whole row of data where that search string was found into a master sheet.

So basically, the script would go to each sheet, search for the string, if it is found it would copy/insert the row from that sheet into a "Master" sheet, and then go to the next sheet and do the same until all of the sheets were processed.

Thanks in advance for your help.

[vcoolio]

Hello Amnya,

By "search string" I assume that you mean a criterion. Is this correct? If so, is the criterion to be found in one column only in all sheets (say Column A)?

Cheerio,
vcoolio.

[amnya]

Yes, i meant the criterion. And the data is not necessarily in a certain column, it can be in either A,B,C or D.

[vcoolio]

Hello Amnya,

How many columns do you have?

[amnya]

A through F, so, 6 colums. Sheets count is around 280.

[amnya]

Actually, i just learnt another peace of info, the criterion should be found either in column C or D only.

[vcoolio]

Hello Amnya,

I think that the following code should do the task for you:-

Sub TransferData()
Application.ScreenUpdating = False
     Dim ws As Worksheet
     Dim lRow As Integer
     Dim SearchID As String
SearchID = Sheets("Master").Range("A1")
'Sheets("Master").Range("A3:G" & Rows.Count).ClearContents
For Each ws In Worksheets
     If ws.Name <> "Master" Then
     Sheets(ws.Name).Select
     
For Each cell In Range("C2:D" & Cells(Rows.Count, "A").End(xlUp).Row)
            If cell.Value = SearchID Then
                lRow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row + 1
                Range(Range("A" & cell.Row), Cells(cell.Row, Columns.Count).End(xlToLeft)).Copy
                Sheets("Master").Range("A" & lRow).PasteSpecial
                Sheets("Master").Range("G" & lRow).Value = ws.Name
            End If
        Next cell
    End If
Next ws
            
Sheets("Master").Range("A1") = "Search"
Sheets("Master").Select
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

I have attached my test work book for you to peruse. For the sake of the exercise, the criteria I have used in the various sheets in the test work book (mixed over Columns C & D) is "Dog", "Cat", and "Canary" (red font). In the "Master" sheet, in the Search box, type in any of the criteria (case sensitive), click on "GO" and the relevant rows from all sheets will be transferred to the Master sheet. In Column G of the "Master" sheet, the code will place the name of the source sheet for you just in case you wanted to know at a glance from where the data came.
I'm not sure if you would like the "used" data cleared from the "Master" sheet before you transfer new data. Let me know your thoughts on this.

I hope that this helps.

Cheerio,
vcoolio.