I would like to write a vba code that does a similar request to the code below. What I would like is for the code to open a new excel sheet and paste the values  into individual cells in a row: Sub CopyAllREP()
'Application.EnableEvents = False

Dim i As Long, lastRow As Long
Dim myRange As Range, myCell As Range
Dim text As String

' Define the range to search for empty cells in column G
lastRow = Cells(Rows.count, "C").End(xlUp).Row
Set myRange = Range("C1:C" & lastRow)

' Loop through each cell in the range and copy the corresponding non-blank rows to a string variable
For Each myCell In myRange
If myCell.Value = "REP" Then
' Empty cell found, add a line of text to separate the results
text = text & "CONTRACTOR - - - - - -, WORK DONE - - - - - -, TASK CODE - , Inv/Qu No. -, COST - -, PR DATE - - -, WORK TYPE - - - -, PR No. -, TIME STAMP - - - - - -, CONTRACTOR CODE" & vbCrLf

' Copy corresponding non-blank rows to text string with commas
For i = 1 To 11 ' Assuming data range is from A to K
If Cells(myCell.Row, i).Value <> "" Then
text = text & Trim(Cells(myCell.Row, i).Value) & ", " ' Add a comma after each column value
End If
Next i

' Trim any extra spaces and add a single line break instead of double
text = Trim(text) & vbCrLf & vbCrLf
End If
Next myCell

' Open a new instance of Notepad and paste the results
Shell "notepad.exe", vbNormalFocus
SendKeys text
Application.CutCopyMode = False

' Show a message box with the results
MsgBox "Results copied to Notepad. Save the file as desired.", vbInformation

'Application.EnableEvents = True

End Sub