Loop formula on one worksheet until empty cell on another worksheet

Hi All,

I am hoping this is going to be very straight forward for VBA gurus, which I am unfortunately not

I have 2 worksheets: Sheet1 called “Inspection Report” which contains cells I need to concatenate into Sheet2 called “WW”.
The number of rows on “Inspection Report” changes with every report so I need to continue the formula in “WW” until an empty cell is found in “Inspection Report” Range E16:E500

This is the very basic code I have – it covers the formula and insert row but I just can’t seem to get the formula to loop through until an empty cell is found...

Sub ConcatenateWW()
'
'Concatenate Barcode, Location and Code from "Inspection Report"
'and paste into "WW" Sheet - under Description of Works - starting in cell B8
'need code to keep adding rows in "WW" and concatenate formula until empty cell
'in "Inspection Report"
'
    Sheets("WW").Select
    Range("B8").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE('Inspection Report'!R[8]C[3],"";   "",'Inspection Report'!R[8]C[6],"";  "",'Inspection Report'!R[8]C[4])"
    Rows("9:9").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

Would appreciate any help

Sub ConcatenateWW()
    Dim Description As Variant, _
        Barcode     As Range, _
        Destination As Range
        
    Set Destination = Sheets("WW").Range("B8")

    With Sheets("inspection report")
        Set Barcode = .Cells.Find("Barcode").Offset(1, 0)
        
        While Barcode.Value <> ""
            With Barcode
                Description = Array(.Value, .Offset(0, 1).Value, .Offset(0, 3).Value)
                Description = Join(Description, ", ")
            End With
            Destination.Value = Description
            Destination.Offset(1, 0).EntireRow.Insert shift:=xlDown
            Set Barcode = Barcode.Offset(1, 0)
            Set Destination = Destination.Offset(1, 0)
            
        Wend
    End With
End Sub

Evidently, you have the word "barcode" somewhere above row 16 so replace the line:
Set Barcode = .Cells.Find("Barcode").Offset(1, 0)

with

        Set Barcode = .Range("E16")

The While loop will exit when an empty cell is found in column E