Vba To Look for a word in a Row and Copy and paste the cells below?

[tonywatsonhelp]

Hi Everyone,

OK This one is hopefully a little more straight forward,

I'm looking for a macro to got to Sheet1 Row 10 And look for the Cells that contain the Name "David" (These will be 7 Cells all toghther if this makes it easier you can just look for the first cell containing "David"

Now all the data id held in Rows 11 to 21 so all I need is a macro that can find the word "David" in row 10 Copy the Cells That are 10 Down and 7 across and paste values into sheet2 cell A1 to G10

Again having big problems as I don't know how to find cells in row 10 that contain the name "David" I really only need to find the first name as I say the data I want to copy will always be From the Word David One Row down So Row 11 to row 21 and 7 columns across including the column that contain the name "David"

So if Cell A10 = "David" then Cells A11:G21 copy paste to Sheet2 A1:G10
However if it was cell H10= "David" then Cells H11:N21 copy paste to Sheet2 A1:G10

and so on.

please help if you can

Thanks

Tony

 

[My Aswer Is This]

I'm happy when I can help people and every time I learn things myself. This forum keeps my brain occupied. Some of the really difficult questions I pass on and then check back to see the final answer. Take care of yourself Tony and it's been a pleasure helping you even though sometimes I don't have the answer.

OK Lots of comments to make here,
Not sure where to start?

First Rick and Trebor, Sorry that you feel this post is a repeat of another, I have tried to explain how this happened and my reasons for doing this in my other thread and will include my comments again at the bottom here so you don't have to go searching for it if you only read this one, I hope it helps to explain my reasoning as I would not want to upset anyone because of this.

The Answer Is, you have helped me so much in the past and you might think your not that an expert but to me your are a genius, some of the solutions you have given me I use on an almost daily basis, So I can not thank you enough. (your code works perfectly, no obvious lag that I could see so thank you again).

Rick, What can I say, Again your help has been amazing and your ability to tweek and imporove on other suggestions is not only welcomed and appreciated by me but I think by those others that post as well. so thank very much your code also worked perfectly as it always does.

Here are the comments I made in my reply to the other post about this being a repeated post, hope it helps you to understand my thoughts on the matter. (there is an irony I'm fully aware of in repeating a reply to a post that was saying I was repeating posts, but I'm going with it! )

My Comments where!

"As for the Thread being duplicated, I'm sorry if you think this is the case but my requests where quite different in my mind,
Maybe to you guys this is "The same as my other thread" but to me they are worlds apart, for example, this thread needs all the formulas up to a certain name converted into values which is exactly what it does, my other thread only wants a small amount of data copied into another area, to me, a very different request as far as im concerned. (And both I needed help on)

Now to make sure I'm not being stupid,, I've compared the VBA code for the 2 threads and I think they are worlds apart! I certainly could not have edited one to create the other, and I do a lot of editing of the codes people help me with so I can use them again on other projects, but this would have been beyond my knowledge. Now to you guys that might sound crazy, but if I can use an analogy!

I'm Sure the Spanish for, "Can you show me where the local toilets are?" uses a lot of the same words and sounds very similar to, "Can you tell me where the local bars and restaurants are located please?" so if your quite fluent in Spanish and know the words for Toilet, bars, restaurant etc. you could probably be helped with the Spanish for the first sentence and work out the second using logic and your own understanding. but if you only know a very small amount of Spanish, and you didn't want to make lots of mistakes, you would probably ask someone to help you with both sentences, even though they may be similar, they are still very different.

The similarity's here are they both require going along a line and looking for a name, then doing something once its found that name, and this is because my brain works in very similar ways when I'm trying to find solutions to problems, so I adopted the same idea of identifying the rows with a header that can be searched for, the "Today" one is so all data before today is no longer a formula, something that was vital for other calculations I was doing in the book and the second is to copy and paste one block of data out of what will be a long string of names and their data and bring it into the second sheet for analysis. and to me these are both very different requests.

I'm not saying you are wrong if you feel the post was repeated, clearly you would not have made this statement if you didn't feel that way so sorry for this, with your skills these two needs are clearly the same, but for me, I felt I needed help with both methods so I asked for both.

I will of course say SORRY, for it was not my intention to upset or annoy any one, I wouldn't know what to do without your help so please don't let this put you off helping me again as you guys are amazing and I really do appreciate all the help everyone has given me."

Once again, thank you so much for your help and I'm very sorry for the misunderstanding.

Tony