That seems to be working great! Let me know if i could improve the code i have so far:

Code:
Public Sub ec_es_validation()
    Dim MyData As DataObject
    Dim myStr As String
    Dim myStrArray As Variant
    Dim i As Long
    
    Set MyData = New DataObject
    MyData.GetFromClipboard
    myStr = MyData.GetText(1)
    On Error GoTo Error_Handling
    
    myStrArray = Split(myStr, vbNewLine)
    
    For i = LBound(myStrArray) To UBound(myStrArray)
        Debug.Print myStrArray(i)
        'do more within For loop

    Next i

ExitHere:
    Exit Sub

Error_Handling:
    MsgBox "The clipboard is empty!", vbCritical
    Resume ExitHere

End Sub
Eventually i want to be able to search a worksheet by the myStrArray(i), if found, get the column its in paste that into a new spreadsheet.sheet1 "A - myStrArray(i)" / if not found it pastes a row on a new spreadsheet.sheet2 in the next unused row. id like the script to be smart enough to know "if spreadsheet is open, paste row on correct sheet, if not open, open excel instance and then paste."