To transfer the data to another sheet, I think that the following code may work for you:-
Dim lr As Long
Dim vSrch As String
vSrch = Sheet1.Range("D1").Value
lr = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 4 To lr
If Cells(i, 3).Value = UCase(vSrch) Or Cells(i, 4) = UCase(vSrch) Then
Range(Cells(i, 1), Cells(i, 8)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
Application.CutCopyMode = False
Application.ScreenUpdating = True
The code searches either Column C or Column D for the value that you type into cell D1 and, if found, will transfer the relevant row of data to sheet2.
Following is the link to my test work book for you to peruse:-
Type a value into cell D1 from either Column C or Column D and then click on the "RUN" button to see it all work.
I hope that this helps.