Search for text, copy offset rows

Question

I wrote a macro to read a .csv file. It places the data in Sheet2.

I need to copy a block of data that begins in the row that follows the text string "BP Error". The data that precedes this string can vary in length, but the block of data I'm interested in is of a fixed size. I'd like to copy this block of data into Sheet1.

I tried doing a sub-routine that would search for "BP Error" but I kept getting stuck trying to do Offset copying. I need the next 18 rows of data following that string of "BP Error" (which is always in Column B).

Example: "BP Error" is found in cell B13, copy cells A14:G31 to Sheet1

You should post some code you have tried, so that others an help — warunapww, May 21 '15 at 17:38
Do you have more than 1 events like that or do you want to find the first BP Error only and copy the following block to sheet one? — Abe, May 21 '15 at 17:43
Just one occurrence per run of the macro. Sorry, should have clarified. — William Anderson, May 21 '15 at 17:50

score 2 · Accepted Answer · answered May 21 '15 at 17:55

Since you have to find only the first "BP Error", the following code would do the job for you (Dont forget to change the worksheet names and check the ranges to see if they fit your needs):

Sub DoYourJob()

Dim readingRow As Long

Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet

Set sourceSheet = ThisWorkbook.Worksheets("YourSourceSheetName")
Set destinationSheet = ThisWorkbook.Worksheets("YourDestinationSheetName")

For readingRow = 1 To sourceSheet.Cells(sourceSheet.Rows.Count, 2).End(xlUp).Row

    If sourceSheet.Cells(readingRow, 2) = "BP Error" Then

        sourceSheet.Range(sourceSheet.Cells(readingRow + 1, 1), sourceSheet.Cells(readingRow + 18, 7)).Copy (destinationSheet.Cells(1, 1))
        Exit For

    End If

Next readingRow

End Sub

Thanks Abe, tweaked it and it seemed to work perfectly. I'll try it on some different data sets. Much obliged! — William Anderson, May 21 '15 at 18:14
No problem, if you still got questions, don't hesitate to ask. See you. — Abe, May 21 '15 at 18:16

Search for text, copy offset rows

1 Answers1

Linked