Want to implement a Macro to Find and Copy to another sheet
Closed
Tim
-
3 Nov 2016 à 11:01
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Moderator Last seen 6 September 2024 - 4 Nov 2016 à 08:30
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Moderator Last seen 6 September 2024 - 4 Nov 2016 à 08:30
Related:
- Want to implement a Macro to Find and Copy to another sheet
- How to copy data from one excel sheet to another - Guide
- Excel hyperlink to another sheet - Guide
- Excel move data from one sheet to another - Guide
- Little alchemy cheat sheet - Guide
- How to copy data to multiple worksheets in Excel - Guide
1 response
vcoolio
Posts
1411
Registration date
Thursday 24 July 2014
Status
Moderator
Last seen
6 September 2024
262
4 Nov 2016 à 08:30
4 Nov 2016 à 08:30
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.
