Search in excel vba and copy data

[kishoremcp]

Hi,

I have a sheet with name sheet1. In that the last row has a sentence like "today we have 13 P2 Fail tickets".
Every time the last row of column B has similar sentence with different numbers.
For example if B34 is the last row which has the above sentence then it should copy from B4 to B34 to a different sheet with name "Final". only if I have "P2 Fail" then only it should copy to final and if the last row dont have P2 Fail and if it has P2 Pass then it should not copy to Final sheet the speficied range (B4 to B34). Here I need to copy the entire row but not only the column of B, I mean from row 4 to row 34.
Is it possible ?

[ExcelTab]

Dear kishoremcp,

First of all, to locate the last row of a column (if it is not always B34), you can use :

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Then, your copy-code will look like this:

Please Login or Register  to view this content.

You can modify the Paste with a PasteSpecial (which I recommend you to do) in order to paste only values, or values and number formats.

Have fun!

[kishoremcp]

Thank you for your help. I am a learner in macros. SO, please tell me how to use the above macros and in which order I have to use?

[ExcelTab]

Hello kishoremcp,

You only have to use the last one.
Type ALT+F11, add a Module, and type the following :

Please Login or Register  to view this content.

Save your workbook.

Then, Type ALT+F8, and select "CopyRowsFail", then click "Run".

Have fun!

PS : there is A LOT of documentation about how to start in VBA (macros) on Internet. It could help you.

[kishoremcp]

Thank's for helping me. I have modified the code as per my requirement to achieve something different. I will be having 4 sheets and it has to search the Fail word in all the sheets of last row and copy the data to the final sheet. It is copiying only from one sheet even though i gave the code to search in second sheet. My requirement is if it find the word Fail in another sheet also then it should give a row gap and then paste the second sheet data to the same Final sheet.

Here is the code which I am trying to use.

Please Login or Register  to view this content.

[ExcelTab]

Hello kishoremcp,

Replace your

Please Login or Register  to view this content.

By

Please Login or Register  to view this content.

In addition, you can loop through your 4 sheets instead of doing one sheet at a time.
Try this optimized code:

Please Login or Register  to view this content.

Have fun !

[kishoremcp]

It is working. Just one modification it requires. It is overlapping one row.I mean if i have data from sheet 1 (row 4 to 10) in which row 4 has headdings and then in sheet 2 (row 4 to 6) in which again it has row 4 as headdinigs it is overlapping with the previous row and hence the headding is missing. Please modify this as after the last row in final sheet it should give two rows gap and then paste the other sheets data.

[kishoremcp]

By using the above macro, it is working but the rows are overlapping. How to avoid that. I need to give a 3 row gap between data which is copied and pasted.

[kishoremcp]

While copying data from all the sheets if the condition met it is enough to copy data from B4:M & Last row. and paste starting from A1 in sheet Final. Then it should leave 2 rows blank and then the next sheet data. Every sheet data will start from B4 and only till column M is required. Till now it is copying the entire row. In all the sheets column A is blank. Please help me if the above thing is possible. The condition as mentioned above is to find a word "Fail" in the last row of everysheet. As the column A is blank and the last row is merged that is from column B.