I would like to be able to search an entire worksheet for specific values and
when these values are found, copy them to a new worksheet. I have found some
examples here that were close, but not exactly what I needed to do.
I have a workbook that contains dozens of tabs with multiple columns of
alpha-numeric combinations. I want to be able to search the workbook for a
particular combination and when it is found, copy it to a new worksheet in a
single columnar list.
Example:
Sheet1 contains 5 columns A:E.
Each column contains alpha-numeric combinations like A1001234567,
B1001234567, C1001234567.
I would like to find A100* anywhere on Sheet1 and copy the cell that
contains it to worksheet A100. It doesn't have to be in any order and
duplicates are fine. I would just like them to be in one column.
Since I have so many tabs to go through, if it could search one tab, then
move to the next it would be GREAT. If not, I can run it on each tab.

Thanks for any assistance you can provide!

Greetings,

Have you seen this?

This code sample searches the columns of a worksheet for the occurrence of a
word ("Hello"). Once matching data is found, it is copied to another
worksheet ("Search Results").

Copy Code
Sub FindMe()
Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 1
'This step assumes that you have a worksheet named
'Search Results.
Set wSht = Worksheets("Search Results")
strToFind = "Hello"

'Change this range to suit your own needs.
With ActiveSheet.Range("A1:C2000")
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

End Sub
"Fleone" wrote in message
...
I would like to be able to search an entire worksheet for specific values
and
when these values are found, copy them to a new worksheet. I have found
some
examples here that were close, but not exactly what I needed to do.
I have a workbook that contains dozens of tabs with multiple columns of
alpha-numeric combinations. I want to be able to search the workbook for a
particular combination and when it is found, copy it to a new worksheet in
a
single columnar list.
Example:
Sheet1 contains 5 columns A:E.
Each column contains alpha-numeric combinations like A1001234567,
B1001234567, C1001234567.
I would like to find A100* anywhere on Sheet1 and copy the cell that
contains it to worksheet A100. It doesn't have to be in any order and
duplicates are fine. I would just like them to be in one column.
Since I have so many tabs to go through, if it could search one tab, then
move to the next it would be GREAT. If not, I can run it on each tab.

Thanks for any assistance you can provide!

carlos,
This works exactly as expected, but doesn't do what I need it to do. The
biggest problem with it is when I move to the next worksheet, any data that
is found that matches my criteria overwrites the data that was written from
the first sheet.
What I need it to do is continue to add to the list on the "Search Results"
worksheet as more matching entries are found on additional worksheets.
I tried changing ActiveSheet.Range to ActiveWorkbook but VB didn't like
that, so I am still looking for a workable solution.
Thanks for your assistance so far, I really appreciate it.

"carlos" wrote:

Greetings,

Have you seen this?

This code sample searches the columns of a worksheet for the occurrence of a
word ("Hello"). Once matching data is found, it is copied to another
worksheet ("Search Results").

Copy Code
Sub FindMe()
Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 1
'This step assumes that you have a worksheet named
'Search Results.
Set wSht = Worksheets("Search Results")
strToFind = "Hello"

'Change this range to suit your own needs.
With ActiveSheet.Range("A1:C2000")
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

End Sub
"Fleone" wrote in message
...
I would like to be able to search an entire worksheet for specific values
and
when these values are found, copy them to a new worksheet. I have found
some
examples here that were close, but not exactly what I needed to do.
I have a workbook that contains dozens of tabs with multiple columns of
alpha-numeric combinations. I want to be able to search the workbook for a
particular combination and when it is found, copy it to a new worksheet in
a
single columnar list.
Example:
Sheet1 contains 5 columns A:E.
Each column contains alpha-numeric combinations like A1001234567,
B1001234567, C1001234567.
I would like to find A100* anywhere on Sheet1 and copy the cell that
contains it to worksheet A100. It doesn't have to be in any order and
duplicates are fine. I would just like them to be in one column.
Since I have so many tabs to go through, if it could search one tab, then
move to the next it would be GREAT. If not, I can run it on each tab.

Thanks for any assistance you can provide!