PDA

View Full Version : Finding a Search String in Multiple Sheets and copying Rows to a new sheet



amnya
07-07-2015, 05:04 AM
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
07-07-2015, 05:32 AM
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
07-07-2015, 05:41 AM
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
07-07-2015, 05:44 AM
Hello Amnya,

How many columns do you have?

amnya
07-07-2015, 07:05 AM
A through F, so, 6 colums. Sheets count is around 280.

amnya
07-07-2015, 07:09 AM
Actually, i just learnt another peace of info, the criterion should be found either in column C or D only.

vcoolio
07-08-2015, 12:54 AM
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.