Want to implement a Macro to Find and Copy to another sheet
Closed
Tim
-
Nov 3, 2016 at 11:01 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Nov 4, 2016 at 08:30 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Nov 4, 2016 at 08:30 AM
Related:
- Want to implement a Macro to Find and Copy to another sheet
- Google sheet right to left - Guide
- Little alchemy cheat sheet - Guide
- How to screenshot excel sheet - Guide
- Excel macro to create new sheet based on value in cells - Guide
- How to copy data from one sheet to another in excel automatically - Guide
1 response
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Nov 4, 2016 at 08:30 AM
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:-
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.
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.
