Loop Code Help

[ScabbyDog]

I want a loop macro that looks at data in column H on sheet2 and if in that column there contains the word Data then I want to copy the entire row where the word data is found and then go to Sheet1 and paste that row in the corresponding row. It needs to be pasted in the row where there is a match for contents in column F. So for example, if the word Data is found in Sheet2 column H row 7 then I want to copy that row and then look at row7 in sheet 2 column F and try and find a match for it in Sheet1 column F and if a match is found paste the row I copied into whatever row the match was found.

[tangangtanga]

Can you please attach a sample workbook. thank you

[ScabbyDog]

sample.xlsm

Here it is attached.

[tangangtanga]

Please try this code.. try this 1.xlsm

[ScabbyDog]

Not exactly what I want. Your code assumes the cell contents to be pasted into are on the same row as where you find Data. In the sample I sent the rows that need to be updated are rows 41 and 42 but these numbers can change so I need a code that looks for the values in column F, then matches that column to sheet 1 column F, then paste the new data.

[ScabbyDog]

Please Login or Register  to view this content.

This is what I have at the minute. It runs fine but doesn't do what I need! Any ideas?

[mike7952]

Give this try.

Please Login or Register  to view this content.

[ScabbyDog]

It gives me an error AutoFIlter method of Range class failed and highlights the line " .AutoFilter Field:=8, Criteria1:="Data"".

[mike7952]

Are running the code from the attached wb? If so be sure the macro is in a standard module and NOT a worksheet module.

[mike7952]

See attached wb

sample_v2.xlsm

[ScabbyDog]

I found the issue. I've added a few blank rows at the top of sheet2 and then it gives me the error. Any way of resolving this regardless of how many blank rows at the top?

[mike7952]

You change this line

Please Login or Register  to view this content.

[ScabbyDog]

Many thanks. Works perfectly. Is there anyway to code it so that it runs no matter how far down the data starts?

[ScabbyDog]

I've just noticed that if sheet2 data begins on row4 andso does sheet1, and I change it to A3, it doesn't work.....strange?

[mike7952]

Not to be rude but why would you have your data starting below row 2?

[ScabbyDog]

As I may be inserting various buttons above the data which will run different macros.

[ScabbyDog]

I will be adding buttons that will run various macros at the top of the sheet so my data wont be able to start in row 2 for spacing reasons.

[mike7952]

Not telling you what you should do, but if it was me I would look into creating a custom ribbon.

You can look at Ron's Website

http://www.rondebruin.nl/ribbon.htm

and this link is on his website to download the editor.
http://openxmldeveloper.org/archive/...mUIeditor.aspx