Find Text then Copy and Paste

[helpexcel]

Hi -
I have data on Sheet1 A:Z. I want to search column A for text that is entered on Sheet2A5. Once that value is found on Sheet1, i want to copy from where its found in A to Z and down 6 rows. So if Sheet2A5 is found in sheet1A30 Sheet1A30:Z36 is copied and pasted to sheet2AB5.

thanks!

 

[Fluff]

How about

Sub helpexcel()
   Dim Fnd As Range
   
   Set Fnd = Sheets("Sheet1").Range("A:A").Find(Sheets("Sheet2").Range("A5").Value, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.Resize(7, 26).Copy Sheets("Sheet2").Range("AB5")
End Sub

 

[helpexcel]

Brilliant as usual @Fluff!!

I was kinda close...but not really. It's so easy, yet so hard.





Dim LastRow As Long
LastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row


Dim Lrng1, Frng1 As Range
Set Lrng1 = ws2.Range("A5")
Set Frng1 = ws1.Range("A1" & LastRow).Find(Lrng1)


If Not Frng1 Is Nothing Then

 

[Fluff]

You're welcome & thanks for the feedback