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

Report
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
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.

1 reply

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!