Copy and Paste with certain criteria

Hello:


Please refer to attached file.

I have excel file with data from A1:T31.

I need a VB macro or Formula to do the following:

Need to check the value in column L.
If value is greater then zero then copy corresponding cell from N:T in sheet2 as shown.

In the attached example since L2 > 0 then i would need to copy and paste in Sheet2 at A2
In the attached example since L3 = 0 then skip.
In the attached example since L4 > 0 then i would need to copy and paste in Sheet2 at A3

asn so on...

Let me know if you have any questions.
Thanks.

Hi Riz,
Try the attached.

Hi AB33:

Works great, thank you so much.

Riz

Riz,
I forget to include a line on the code if you do not have visible filtered cells to copy. The amended code works better.

Hi AB33:

Thank you so much for updated code and i have tried and this works great.
I need 1 more favor.
Need to modify the code so t give the total rows copied.
i need this is field P6.


Let me know if you have any questions.
Thanks.

Riz

Hi Riz,
You mean sheet 2, P6.

Hi AB33:

Yes, thank you so much.
Will let you know if any further help is needed.
Thanks

Riz

Hi AB33:

I am trying to implement your code in my worksheet and for some reason, when i run the macro, excel closes.

Please refer to attached file.
I would really appreciate if you could look into and let me know if I need to make any further changes.

Basically, I am creating excel file to print Pay check for employees.


Let me know if you have any questions.
Thanks.

Riz

Hi Riz,
My PC has keept crashing when I try to work on your sample. I am unable to delete or amend the sheets. As soon as I do something on sheets, excel crashes-5 times. It may be something to do with the link of sheets you have with other sheets. I need to my excel 2010

I have put the code on the top. I assumed that you want to copy the data in colulmn B and columns are now streched to column V.

Hi AB33 and All:

Its very strange why excel is crashing.
I do not have any links from other workbook.
Its also crashing at my end.
Please have a look and let me know if you have solutions.
Thanks
Riz

It is possible some of the sheets have been corrupted. The simple solution is to copy each sheet in to new sheet, save it with a new name and then delete the culprit (Old book).

Hi AB33:

I will try that out and let you know later.
Thanks a lot for your great help.
Riz

Hi AB33:

I have tried with your instruction, still same result...excel closes/crashes.
It looks like its something to do with VLOOKUp, since part of the Vlookup table is erased.
I need to try something and need your help.

Look at the your code below:

Please Login or Register  to view this content.

Here the line :

Please Login or Register  to view this content.

will be pasted at the end of previously stored data.
I need overwritten.
The max overwritten will be 30 rows.
Lets say you have 4 employees with Net Pay >0 then those 4 records will start from "Sheet2", B1,B2,B3,B4.
The other cell B5 thru B30 can be null.
I think overwrite will solve my problem of clearing the cells.

Let me know if you have any questions.
Thanks.


Riz

Hi AB33:

I believe i have solved the problem.
I will contact you if further help is need.

Thanks

Riz