Previous Topic Next Topic

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

Closed
Tim - Nov 3, 2016 at 11:01 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Nov 4, 2016 at 08:30 AM
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 response

Answer 1 / 1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Nov 4, 2016 at 08:30 AM
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.
0