CODEWITHSUNDEEP
excelvba
William Anderson
William Anderson

Reputation: 13

Search for text, copy offset rows

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

Upvotes: 1

Views: 702

Answers (1)

Abe
Abe

Reputation: 274

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

Upvotes: 2

Related Questions