Unanswered: Find, Copy, Paste... with Macro in Excel
I'm doing a small project on which I need your help. I would be obliged if anyone can help me on this.
I have a excel sheet which has some data. And I'm trying to find a word “subs” in it and then copy all the cells which has this word and then and paste it in different sheet in a row and if I don’t find it should return a value/msg. I'm almost done with the below code:
Application.ScreenUpdating = False
Dim rngFound As Range
Set rngFound = Cells.Find(What:="subs", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
If Not rngFound Is Nothing Then
MsgBox "No relevant data."
The problem is, if the word “subs” is repeated more than one time, it is just pasting the first one. And if I run the macro again, it is pasting the second. What I want is, it should be in loop, it should copy all the cells which has the word “subs” and paste all of them in different sheet in a row.