Search open files for string and then copy that row into a new workbook

[taylordude]

Hello,
I am trying to develop a vb program for Excel which will search through open workbooks for a text string (let's say that string is "error" for example) in a cell. Once this program finds this string, I need it to copy that cell and adjacent cells into a different workbook (this will be used to summarize the errors). As it copies this information, it would be nice if it stepped down the page as it pasted (so all the error cells wouldn't be pasted on top of one another). I am pretty new to vb and have been searching the web for a similar post but have not found it. I really appreciate any help you can give!

Thanks,
nick

[jeffstl]

Hello,
I am trying to develop a vb program for Excel which will search through open workbooks for a text string (let's say that string is "error" for example) in a cell. Once this program finds this string, I need it to copy that cell and adjacent cells into a different workbook (this will be used to summarize the errors). As it copies this information, it would be nice if it stepped down the page as it pasted (so all the error cells wouldn't be pasted on top of one another). I am pretty new to vb and have been searching the web for a similar post but have not found it. I really appreciate any help you can give!

Thanks,
nick

Are you creating a seperate .exe that will perform this job?...or are you developing the VB Code in Excel (VBA) ?

Pretty tall order....I don't think anyone will post the work for you so you will probably be better off getting started and getting down to the specific problems you run into.

I do have a huge VBA app written in excel that reads various cells and generates graphs, etc, but none of this is pretty and its all completely dependant on individual format\design of the actual spreadsheet your working with.
Try this I guess for now and see how you do:
http://www.excel-vba.com/excel-vba-contents.htm

[taylordude]

Ok here's what I have so far. It's an amalgamation of many snippets I've found online so far:

ub test()
Dim wbk As Workbook
Dim sht As Worksheet

For Each wbk In Workbooks
For I = 1 To Worksheets.Count
Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 4

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = "Mail Box", copy entire row to Sheet2
If Range("B" & CStr(LSearchRow)).Value = "Failed" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Workbooks("ProjSummary.xls").Sheets("Sheet2").Sele ct
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."
Next
ActiveWorkbook.Close
Next wbk
End Sub


I am trying to thumb through all of the open workbooks looking for rows containing the word "Failed". Once found, I am trying to paste that row into a summary sheet (ProjSummary.xls) and step down to the next row so that when "Failed" is found in another workbook, it won't past on top of the previously pasted row. I think this code is close, but I am getting some errors. Thanks for the help!