Sub ExtractDataToARRRange() Dim SourceWs As Worksheet Dim TargetWs As Worksheet Dim LastRow As Long Dim TargetCol As Long Dim Found As Boolean ' Set the source worksheet and initialize the target column Set SourceWs = ThisWorkbook.Worksheets("ARR Input") On Error Resume Next Set TargetWs = ThisWorkbook.Worksheets("ARR_Range") On Error GoTo 0 ' Create the target worksheet if it doesn't exist If TargetWs Is Nothing Then Set TargetWs = ThisWorkbook.Worksheets.Add TargetWs.Name = "ARR_Range" End If TargetCol = TargetWs.Cells(1, TargetWs.Columns.Count).End(xlToLeft).Column + 1 ' Find the next available column in ARR_Range ' Loop through each cell in column X of the source worksheet For Each Cell In SourceWs.Range("X1:X" & SourceWs.Cells(Rows.Count, "X").End(xlUp).Row) Found = False ' Check if the cell contains "" If InStr(1, Cell.Value, "", vbTextCompare) > 0 Then ' Start selecting rows below until "Completed BY RM" is found Do Set Cell = Cell.Offset(1, 0) SourceText = Cell.Value If InStr(1, SourceText, "Completed BY RM", vbTextCompare) > 0 Then Found = True Exit Do End If Loop Until Cell.Row > SourceWs.Cells(Rows.Count, "X").End(xlUp).Row ' If data is found, copy and paste it into the target worksheet If Found Then Cell.Resize(1, 1).Copy TargetWs.Cells(1, TargetCol) TargetCol = TargetCol + 1 End If End If Next Cell End Sub