Copy and paste on a criteria VBA /& other

[jevi]

Hi All,

1. I have a big database and I need to copy from this huge database from column A to BZ, the rows differ everyday so now is 133.458. From this file "Database.xlsx", sheet will have an incremente number every day but is the first sheet always, I need to copy in another file "Working File.xlsx" sheet "Dati" the columns D, E, F,G and AM and the criteria is: exclude the value "0" (the column AM is with values and I need to exlude the "0").

2. Then when the data is copied to the "Working File.xlsx" sheet "Dati", I need to remove dublicates based on Column E,F,G,AM (which in the new file and sheet the columns will be A:E.

Then to make it perfect don't know if it is possibile with VBA (but important for me is 1 & 2):
- count the names that are in column D and the amount that is in the column E and give the result in a new sheet but same "Working File.xlsx" in cell B5;B6
- give the information from column A:E but for only the 10th clients with the highest value (that in is column E or old AM).

I try to record it but is not working as too many values when I uncheck the 0.

I hope I was clear but please let me know if you need more explanation
Thank you,

 

[jevi]

Thank you...back to the office. I tried your macro, and it is almost great but I see that it still has some duplicates in it. I checked it with my manually version and there were duplicates and when I did remove duplicates you had the same values as I do. They were not all duplicates but 78 ID were double.

I try to check your code and it sound correct like the criteria to remove the duplicates is i: 6,7,39...so don't understand why. But I check an ID and in your file is 4 times and in mine only 1..which is correct as it is in our IT system. And then also when I used remove duplicates function from excel with the criteria 6,7,39 it stayed only 1 as in my list. So don't know if the macro sees something else

I will give a try also tomorrow.

 

[DanteAmor]

I don't understand, do you have problems with the macro and some records? If so, I would have to go through the data and see what is happening.
Share your file on the web.
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Highlight in yellow the records with which you have problems.

 

[jevi]

Sorry for the late reply but messy period. I did test the macro today and I see that it does copy the values from "Working File.xlsx" sheet "Dati" the columns D, E, F,G and AM and exclude the value "0" as I wanted but it doesn't remove the duplicates based on Column F,G,AM.

In this part of VBA I can't understand if you have given the criteria of only column AM different from <>0, or also different from zero column AM (first criterio), then remove the duplicates of column 6,7,39 (as a second criteria)

 'columns F, G and AM
    If Not IsError(a(i, 6)) And Not IsError(a(i, 7)) And Not IsError(a(i, 39)) Then
      ky = a(i, 6) & "|" & a(i, 7) & "|" & a(i, 39)
      If a(i, 39) <> 0 Then

On the weekend I can upload a sample of data as I have sensibile data so I have to change some codes.

Thank you so much..sorry is such a burden this file.

 

[jevi]

Hi DanteAmor,

sorry the late reply but I moved to a new home so busy period. I did marked it as a solution as with your help and a registered macro I make it work out.

Thank you for the help.

Ciao