find specific text in row one then copy from that cell down

I have searched EE but cannot find the specific code I need. Would appreciate the help of an expert.

My specific need is to find the word 'book' in column 'B' then move 6 to the right then copy all cells from that cell down to the last cell that contains data.

Many thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
Does it have to be "book", or also when it's part of that cell like "abcbookdef" ?
Does "down to the last cell that contains data" mean that if "book" is found in B3, and column B runs through B100, that H3 should be filled through H100 (copied from B3:B100)?
Or did you mean Only fill H4 when the next "book" is encountered in column B?
0
JagwarmanAuthor Commented:
Hi kimputer,

in this instance it will be Book, so, if 'Book' is found in B3 I need to move 6 to the right and then copy from H3 to H100

If column B runs through B100, then I need to copy H3 through to H100 I will then Paste this data in column A in Sheet2 cell A2
0
Rgonzo1971Commented:
Hi,

pls try

Sub Macro2()

Set Rng = ActiveSheet.Range("B:B").Find(What:="Book", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not Rng Is Nothing Then
    BookRow = Rng.Row
    Range(Range("H" & BookRow), Cells(Range("B" & Rows.Count).End(xlUp).Row, "H")).Copy Destination:=Sheets("Sheet2").Range("A2")
End If
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Thanks Rgonzo exactly what I was looking for
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.