Searching 3 columns of values greater than 0 then pasting it to another sheet

[xjpx]

Hi all, I recently started VBA for Excel for a work of mine but I haven't been able to carry out a task I am doing. I hope someone could help me out. The procedure is as follows:

1. Search columns "BR" , "BU" and "BX" for all values above 0.
2. Copy all these values and paste it in a column of another sheet of the same workbook, "Sheet 2"
3. For all the searched values, copy another set of data in the same row as it in column "GU" into "Sheet 2"

I hope my explanation is enough. If anyone requires more information please let me know.

John

 

[xjpx]

Option Explicit
Sub test()
Dim Lr1&, Lr2&, Lr3&, i&
Dim cell As Range
Dim arr(1 To 1000000, 1 To 2)
With Sheets("Sheet1")
    Lr1 = .Cells(Rows.Count, "BR").End(xlUp).Row
    Lr2 = .Cells(Rows.Count, "BU").End(xlUp).Row
    Lr3 = .Cells(Rows.Count, "BX").End(xlUp).Row
    For Each cell In .Range("BR2:BR" & Lr1)
        If cell > 0 Then
            i = i + 1
            arr(i, 1) = cell
            arr(i, 2) = cell.Offset(, 133)
        End If
    Next
        For Each cell In .Range("BU2:BU" & Lr2)
            If cell > 0 Then
                i = i + 1
                arr(i, 1) = cell
                arr(i, 2) = cell.Offset(, 130)
            End If
        Next
            For Each cell In .Range("BX2:BX" & Lr3)
                If cell > 0 Then
                    i = i + 1
                    arr(i, 1) = cell
                    arr(i, 2) = cell.Offset(, 127)
                End If
            Next
End With
Sheets("Sheet2").Cells(2, 1).Resize(i, 2).Value = arr
End Sub

@bebo021999 Hello, is it possible to search for the column headers instead of BR BU and BX? Because my data has shifted multiple times since January and I am constantly changing the alphabetic headers. I tried changing the solution to the name of my header but it does not work for me. The header I want to search for are Fault[0], Fault [1] and Fault[2]. Do I do up a new post or can i do some edits in this solution? Thanks,