Need a macro to copy data from one sheet to another based on condition.

**kishoremcp** · 06-19-2013, 09:43 AM

I have an excel file where there are 6 sheets with names (Final, Sheet1, Sheet2, Sheet3, Sheet4, Mail).
In Final Sheet I am taking some values in a table which are calculated from all the remaining sheets 1 to 4.
Now what I want is if the table column headding is P1 or P2 and row headding as Fail then it should copy and paste the corresponding data from the corresponding sheet. (Range always starts from B4 and ends with the last filled row of that sheet)
If both P1 and P2 values with Fail headding exists then it should copy data one below the other starting in Mail sheet from cell A10). The copied data should be autofit.

All the sheets from 1 to 4 are imported at the time of requirement. So, the data is not static. it is dynamic.

Attached is the test file. Please help.

**kishoremcp** · 06-20-2013, 02:14 AM

I have an idea how to do it but I am not able to write the macro. Please help with the below inputs.

Always the last filled row of all the sheets 1 to 4 has a line in which it is mentioned how many P1 or P2 are there with pass or fail.
So by taking that line as the validation, we can get the data to the required mail sheet to A10.

So, it should be like this i think

check if the last row of sheet 1 to 4 has any fail word then copy and paste from B4 of that sheet to the last filled row with autofit to the mail sheet starting from A10.
Then in loop again it has to check for fail in all the sheets and do the same thing again..

If we have to copy morethan 2 sheets of data to mail sheet (I mean on a day we have both P1 and P2 fail then we have to copy both the sheets in mail sheet)
So in that case we have to calculate the last filled row of sheet mail so that to copy the second failed sheets data in the same sheet by leaving 2 or 3 rows below the data in sheet mail.

If I am able to clearly explain the logic, please help me. Because I am not a software guy...

Thanks in advance...

**kishoremcp** · 06-20-2013, 03:14 AM

Any help please

**kishoremcp** · 06-20-2013, 03:43 AM

any idea please

**kishoremcp** · 06-20-2013, 09:43 AM

See, I have 4 sheets (sheet1 to sheet4).

In all the sheets the last row is where the summary is stored. If that summary has the word "Fail" then it should copy from row 4 till the last row which is the summary row. same like that it has to check for all the sheets and do the same thing.

If there are multiple sheets which has Fail word in the summary row then it should copy both the sheets rows to a the final sheet which is named as Mail. If there is no "Fail" word in any of the sheets then it should do nothing.

Did I tried my best to explain my requirement?

Sorry for giving trouble....

I am not an expert...