help--excel macro

  • Thread starter Thread starter automate
  • Start date Start date
A

automate

hello

help with excel


I cut&paste info into excel, using the find function
I locate a keyword then select a few rows of data below
the keyword and paste in another sheet .The keyword is in
different locations not in any order.

how using a macro
example
after using the find (the keyword)
get the macro to select the 6 rows below the keyword
so then i could paste in other location.

thanks
 
A bit shorter, without all the selects. Change the A1 to wherever you want
to copy it to

Dim Lrng As Range
Set Lrng = Worksheets("Sheet1").Cells.Find(What:="YourKeyWord", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Lrng Is Nothing Then
Lrng.Offset(1).Resize(6).EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
 
thank you it works great

Bob Phillips

what do i need to change
instead of 6 entire rows
I just want the 6 cells below the keyword?

thanks again

Dim Lrng As Range
Set Lrng = Worksheets("Sheet1").Cells.Find(What:="YourKeyWord", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Lrng Is Nothing Then
Lrng.Offset(1).Resize(6).EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
 
This should do it.

Dim Lrng As Range
Set Lrng = Worksheets("Sheet1").Cells.Find(What:="YourKeyWord", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Lrng Is Nothing Then
Lrng.Offset(1).Resize(6, 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
 
Back
Top