Help with Data Extract

[Sean Evanovich]

I'm not even sure this can be done, but I think is should
be possible.

Can you open a mutiple sheet/tab workbook and search each
sheet for rows with a specific value in column A, and
extract each of these rows to a text file?

What if you don't know don't know how many sheets there
are in the workbook, or if the names of the sheets are
not standardized?

Any assistance would be greatly appreciated...

Thanks,
Sean

 

[Tom Ogilvy]

for each sh in Thisworkbook.worksheets
set rng = sh.Cells.Find(What:="Target")
faddr = rng.Address
if not rng is nothing then
' write to text file
set rng = sh.Cells.FindNext(rng)
Loop until rng.Address = faddr
Next

I doubt you want to write the target string in a text file N times, so I
can't say what you should do once the cell is found in each case.

See help on the Find Method for more arguments that might affect your
search.

See this article for help on writting to a text file (or you could write the
results to a worksheet in a new workbook and then do a SaveAs with a text
fileformat.

http://support.microsoft.com/support/excel/content/fileio/fileio.asp
File Access with Visual Basic® for Applications

 

[Sean Evanovich]

Thanks Tom...that seemed to do the trick...I modified it
slightly to look like this...WriteRecord sends my row to
a text file...

Public Sub macro1()

For Each sh In ThisWorkbook.Worksheets
With sh.Range("a1:a999")
Set rng = .Find("A ")
If rng Is Nothing Then
GoTo None_Found
Else
faddr = rng.Address
Do
WriteRecord
Set rng = sh.Cells.FindNext(rng)
Loop Until rng.Address = faddr
End If
End With
None_Found:
Next