Want to implement a Macro to Find and Copy to another sheet Closed

Question

Hello, 
I have tried to implement a Macro on Excel for my data,  I have a search box made for my data on  cell D1 

I want to make a macro in which if the text am looking for in Cell D1 is present in either Column C or D starting from ROW 4. and if its found then copies the whole row but Only from A to H.

Kindly can you help me with that.

vcoolio · Answer

Hello Tim,

To transfer the data to another sheet, I think that the following code may work for you:-

Sub Search()

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)
    End If
Next

Range("D1").ClearContents
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

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:-

https://www.dropbox.com/s/0dalaaswsnr9j6q/Tim%28ForNext%20Loop%20on%20two%20columns%29.xlsm?dl=0

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.

Cheerio,
vcoolio.

Want to implement a Macro to Find and Copy to another sheet

1 response

Similar discussions

Subscribe To Our Newsletter!