continuous if not the element is not there

[pat4ever]

Hi ALL,

i am a new in vba programming and have a problem to solve.
i have a excel file containing a column with many users and another column with differents kind of elements.(i receive a new list every month and some time not all users are in the list that i receive).
i have to filter the table (the column User) and for every user creat a new sheet (in the same excel datei) with the elements conserning the user, copy all the table and past it in the new sheet. for the user i also have to filter the elements ( let say the nom of the header ist Done )after that i have to open another excel file, copy some elements (numbers) in somes cells there and uses that to make some mathematics operations in the new created sheet. My code can filter the users and also the elements but i still have this problems:

1- my code filter the users and the elements according to name of the column but if the column with users changes, the code will not work anymore. How to loop the header according to the name of the column and element ?

2- the code i wrote stop when he don't find a name of the user instead of continuing filter the column.(i wrote a code for each users (another option is welcome ) and when the name of a users don't appear the program stop). How can i tell the code to continue filter even when somes users are missing? (it is can be possible that the user din't works or is not there anymore )

3- if the number of users increase it will not be a good idee to write a code for each user (beacause the process is the same), how can i fix the problem?

4- how to pic a number in a closed cell and use it to make some mathematic operation in each cell for each user?.

my problem looks big but like i said my code can already filter the name, filter different elements and give to total of each elements.(i just want to upgrade it to avoid problem if oneday day the file looks different)

i will be very thankfull for your help.

here my code (for one person):

Sub filter()
With ThisWorkbook

'Ma, Ku, Up, NW are the name of the elements i want to count to total of

Dim ws As Worksheet
Dim adr As Range
Dim CountMa As Integer
Dim CountSp As Integer
Dim CountKu As Integer
Dim CountUp As Integer
Dim CountNW As Integer
Dim wb As Workbook

' create a new sheet

Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
ws.Name = "ANDR29" ' name of one user

' look after the name according to the range

Worksheets("Excel DMS").Range("E1").AutoFilter _
field:=5, _
Criteria1:="ANDR29", _
VisibleDropDown:=True

' copy the value in Excel DMS and paste it in a new sheet

Worksheets("Excel DMS").Activate
Range("A:I ").Select
Selection.Copy
Worksheets("ANDR29").Activate
Range("A1").End(xlUp).Select
ActiveSheet.Paste

' insert the function, count the number of elements and give the result in a cell

Set adr = Worksheets("ANDR29").Range("F2:F1000")
CountMa = Application.WorksheetFunction.CountIf(adr, " Ma ")
Debug.Print CountMa
Set adr = Worksheets("ANDR29").Range("P2")
adr.Formula = CountMa



Set adr = Worksheets("ANDR29").Range("F2:F1000")
CountSp = Application.WorksheetFunction.CountIf(adr, "Sp ")
Debug.Print CountSp
Set adr = Worksheets("ANDR29").Range("Q2")
adr.Formula = CountSp

Set adr = Worksheets("ANDR29").Range("F2:F1000")
CountKu = Application.WorksheetFunction.CountIf(adr, " Ku ")
Debug.Print CountKu
Set adr = Worksheets("ANDR29").Range("R2")
adr.Formula = CountKu

Set adr = Worksheets("ANDR29").Range("F2:F1000")
CountUp = Application.WorksheetFunction.CountIf(adr, " Up")
Debug.Print CountUp
Set adr = Worksheets("ANDR29").Range("N2")
adr.Formula = CountUp

Set adr = Worksheets("ANDR29").Range("F2:F1000")
CountNW = Application.WorksheetFunction.CountIf(adr, " NW ")
Debug.Print CountNW
Set adr = Worksheets("ANDR29").Range("O2")
adr.Formula = CountNW


End with
End Sub