Search all worksheets within a spreadsheet for a value and copy data to another sheet

[Panda]

Hey all,

I have a workbook with over a 100 worksheets, can some code be written to autofilter by a value, and then search all the worksheets for this value and copying the data from each of the sheets where the value is found to another woroksheet?

Thanks

Phil

[ntrauger]

Your description is pretty vague, but I bet this will help get you on the right track.[vba]Sub SearchAllSheets()
Dim ws As Worksheet, dest As Worksheet
Dim c As Range
Dim firstAddress As String
Dim i As Long
Dim val
val = "Data to Search For" 'String, date, or whatever.
Set dest = Sheet3 'Destination sheet.
i = 1
For Each ws In Worksheets
If ws.Name = dest.Name Then GoTo skip
Set c = ws.Cells.Find(val)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Copy dest.Cells(i, 1)
i = i + 1
Set c = ws.Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Set c = Nothing
End If
skip:
Next
End Sub[/vba]
Edit: Something to be aware of is that this code may give unexpected results if your target data occurs in more than one column in the same row on the same sheet.

[shrivallabha]

Even if the data is in many columns and you are concerned with a specific column say 1 then you can edit above code as:
[VBA] Do
If c.Column = 1 Then
c.EntireRow.Copy dest.Cells(i, 1)
i = i + 1
End if
Set c = ws.Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
[/VBA]

[Panda]

Hey guys,

Thanks for your replies, with the modification this works a treat sorry about the vague explanation will be clearer when I post next time.

Thanks again for your help saved me manually autofiltering and copying data from over a hundred worksheets!!! =

Phil

[ntrauger]

Glad to help. Don't forget to take a second and mark the thread as solved using the "Thread Tools" menu at the top right of the page.