Selected Answer
This is the code to help you write your own code. It is fully functional, but its aim is to show you all the information that is needed which makes it somewhat more convoluted (but much easier to understand) than one would write it for practical purposes.
Sub CopyToOtherSheet()
Dim FoundValue As Variant
Dim WsTo As Worksheet ' the worksheet to paste to
Dim Rt As Long ' row to paste to in WsTo
Dim Ct As Long ' column to paste to
Dim WsFrom As Worksheet ' the sheet to copy from
Dim Rng As Range ' relevant part of column B
Dim Rstart As Long ' first row of Rng
Dim Rend As Long ' last row of Rng
Dim R As Long ' row counter for loop
Set WsFrom = ActiveSheet
Rstart = 2 ' first row below caption ?
With WsFrom
Rend = .Cells(.Rows.Count, "B").End(xlUp).Row ' last used row ?
For R = Rstart To Rend
If Val(.Cells(R, "B").Value) <> 0 Then
FoundValue = .Cells(R, "A").Value
Exit For
End If
Next R
End With
Set WsTo = Worksheets("Sheet2")
Ct = 3 ' 3 = column C ? (1 = A)
With WsTo
Rt = .Cells(.Rows.Count, Ct).End(xlUp).Row + 1 ' below the last used row ?
.Cells(Rt, Ct).Value = FoundValue
End With
End Sub
Start with the list od 'Dim' statements at the top. All of this information will be required. You can fill in the information is you read down the code. In some cases the information may be dynamic, such as the last used row. My code shows you how you might get that information programmatically.
When you are done you may like to streamline the code by eliminating some of the declarations. That will shorten the code but not necessarily make it either faster or better.