Macro is not working as per the offset and hence missing data..

Hi,

I have the below macro which is working good. But the problem is once the data from sheet1 (if condition satisfies) is copied to the sheet Final then it is checking in second sheet for the condition.
If the condition is satisfied then it is copiying the data and pasting in sheet Final, but it is starting from the same row without verifing the old existing data.

Ex: If condition satisfies then,
it copies data from sheet1 and pasting in sheet final starting from row2. and then searches in sheet2 and again copiying data in sheet final row 2. which should not do.
I want the macro to paste data in the sheet final by giving a 2 row gap and then paste the data from sheet2 to final sheet.
All the sheets from 1 to 4 has merged columns and the column A is empty.

I have a loop of 4 to 5 sheets and the condition is mentioned in the macro.

Please help

Please Login or Register  to view this content.

any idea / suggestions or help please

xlvalues is missing from paste

Please Login or Register  to view this content.

No, still the same problem.

I do not know how the code is working.
We know you are looping 4 times, but which column are you testing for "Fail"? Is it column B?

Please Login or Register  to view this content.

Yes, it is in column B.

You can not test the whole column B, you need to test each row.
I would start like this

Please Login or Register  to view this content.

This test for each row from 1 to 4 in column is fail then do something

I am not good in excel macros. so could you help me with the complete code with modifications.

Okay! as I do not fully understand your request, we will take it step by step.
This code will loop 4 times, but we will change it to make dynamic once we know the issue.
If tests column B for fail, if it is true, it copies column B- column E and paste them starting in row 2 final sheet data.

Please Login or Register  to view this content.

One more question.
In which row do the value in column B start? Is it row 1? Do you want to go down from row 1 until the last row of column B and test each row?

Row 4 where column B starts and we are finding the last row with condition.

While using your full code i am getting an error runtime error 438 (object doesn't support this property or method).
If I debug it is highlighting the line
LR = .Cells(.Rows.couunt, 2).End(xlUp).Row

Typo

Please Login or Register  to view this content.

Please Login or Register  to view this content.

It is not copying any data from any sheet. The macro runs without any error but not copying any data.

If it is true which column/s in that row you want to copy? How many columns?

You are also converting your data in column B to upper case for a test.

Please Login or Register  to view this content.

Do you rows in column B which have fail word?

I want to copy from column B to M in which there are merged columns in the original file which I am getting from an automated email. Sometimes if i write a macro to copy column wise it is giving me an error that i cannot copy because the size of the column differs and there are merged cells in that.

No choice, you need to unmerge the columns. The code will not find fail if column B is merged cell. After the unmerge, you would notice, the data you are looking is not longer in column B, it has moved somewhere.

After merge
Use this line

Please Login or Register  to view this content.

How do i do that?

Do it manually.
Home- Merge and centre- unmerge

Even after unmerging the entire sheet the macro is not working.

Please attach your sample, so that I can see what is going.
I suspect following unmerge, your data which was in column B is moved to somewhere.

Here is the sample file...I have removed the details in the cells of the attached file. There will be data in those cells in all the sheets.

Misspelt the name final.
Try the attached.

Hey,

Thanks really for helping me. If you see the sheet "Required output like this" it has the table along with headdings. your output is entirely different, I need it along with the format and coloring also.
Please see the "required output sheet and do the needful.

I do not know which row and sheet no go in to which row? You need to show me for e.g. the value from sheet1 goes where in the output?

If the last row of sheet1 has a word fail then it should copy the data from that sheet and paste in the final sheet starting from A1. (Ex: if the sheet1 has fail word in row 34 then it should copy from row 4 to row 34 to final sheet A1 and paste it there. THen look for fail in second sheet and if found agian the same way it should copy but paste after leaving 1 or 2 rows in final sheet below the previous data.

In the example that is the original file just the cells data i have deleted but the last row data i haven't deleted.

The last row in sheet 1 is 58 yet we are looping from row 4 to 58. Or do you mean that all "fail" will be found in row 4 each sheet and copy from row 4 to last row of each sheet. What happens if fail is found again in row 5, do we copy again from 5 to last row? If we do, it is duplicate. For e.g in sheet 4 fail is found in 2 rows, rows 5 and 7. Need more clarification.

It should copy from row 4 to 58 from sheet1 only if the row58 has the word fail. Where in the given attachment it dont have faiil word. SO, now it should go to sheet2 and search. it has the word in row 10. so it should copy from row 4 of sheet 2 to row 10 and paste in final sheet starting A1 with format of color and all. then it should search in sheet3. but sheet 3 last row dont have fail word. so now in sheet4, again it has word fail. so it should copy from row 4 of sheet4 to row 7 of sheet 4 to final sheet. Now while copying this data it should paste after giving a row or 2 rows gap because already sheet 2 data is existing.

The fail word may also be in sheet1 or sheet3 sometimes. I hope I am clear this time.

I am not sure I am getting what are you trying to do. In sheet 2 when the code loops from row 4 to last row, it found fail twice, so if I copy until the last row, I copied duplicates. You need to show me from each sheet where in the final sheet.
Even if I change the code using find function, we will have the same problem as fail in each sheet is found more than 1 times.

I think i am giving you lot of trouble. I am sorry for that. I will explain everything in another example tomorrow because i will be getting another file by tomorrow. Sorry again and really thanks for helping me..

Do not worry!


Since we are looping from row 4 to 58, if we find fail in row 4 and want to copy from 4 to 58, it is fine as long as one fail is found in row 4, what happens if there are fail in each row from 4 to 58? Why do you want to copy from row to 58? Why not each row at a time? Row 4 if fail is found, then if fail is found in row 5, we copy row 5 too, then test each row until 58 and go in to next sheet. What I do not understand is why do you want to loop from 4 to last row and yet you want to copy from the row fail find until the last row. I hope you see the logic with looping. You probably do not need to loop, use find and search function, when fail is found, you copy from the found row until last row. This even assumes that you do not want to search fail again on the sheet and copy again until the last row. You have the same problem as looping.
so, Think!