Find, Copy, Paste... with Macro in Excel

Sirajuddin · #1 (**permalink**) 01-20-09, 07:44

Hi All,

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:

Code:

Sub CommandButton1_Click()
        Application.ScreenUpdating = False
    Dim rngFound As Range
    
    With Sheets("Sheet2").Range("A1:J65336")
        Set rngFound = Cells.Find(What:="subs", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
       
        If Not rngFound Is Nothing Then
            rngFound.Select
            Selection.Copy
  	Sheets("Sheet1").Select
    	Range("A10").Select
    	ActiveSheet.Paste
    	ActiveSheet.Paste
Range("A5").Select
    
        Else
             MsgBox "No relevant data."
        End If
    End With
   
End Sub

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.

Can anyone help me on it and if possible send me the answer on Mohd_Sirajuddin@Dell.com

Internet Services

Web Development

Digital Marketing

Consumer Tech