Sub Compare_2_fichiers_Phase1()
t1 = Timer
NWC = ThisWorkbook.Name 'Name Workbook Comparaison
'Stop
Dim myrange As Range
Dim NW1S(1000), NW2S(1000)
'On Error Resume Next
Range("A2:z1002").Clear
FirstLig = 3
'Affiche toutes les feuilles des classeurs
For i = 1 To 2
ActiveWindow.ActivateNext
'Affiche toutes les feuilles Classeur1
nc = ActiveWorkbook.Sheets.Count
For N = 1 To nc
Sheets(N).Visible = True
Sheets(N).Select
Next
NW1 = ActiveWorkbook.Name
NW1P = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
ActiveWindow.ActivateNext
'Affiche toutes les feuilles Classeur2
nc = ActiveWorkbook.Sheets.Count
For N = 1 To nc
Sheets(N).Visible = True
Cells(1, 1).Select
Next
NW2 = ActiveWorkbook.Name
NW2P = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
ActiveWindow.ActivateNext
'Condition avoir que les 3 fichiers pour continuer (goto suite) sinon sortie (end)
If ActiveWorkbook.Name = NWC Then GoTo suite Else MsgBox "Vous devez fermer les fichiers non utiles": End
Next
suite:
'MsgBox "Comparaison " & NW1 & " et " & NW2
Cells(2, 1) = "Comparaison " & NW1 & " et " & NW2
Cells(3, 1) = NW1P: Cells(3, 2) = NW1
Cells(3, 5) = NW2P: Cells(3, 6) = NW2
'Boucle Feuilles sur fichier 1
For i = 1 To Workbooks(NW1).Worksheets.Count
NW1S(i) = Workbooks(NW1).Worksheets(i).Name
Set myrange = Workbooks(NW1).Worksheets(i).Range("A1:IU65536") '.SpecialCells(xlCellTypeFormulas, xlNumbers)
Tvide = Application.WorksheetFunction.CountBlank(myrange) 'compte vides
TCelNonvide = Application.WorksheetFunction.CountA(myrange) 'compte Nombre de cellules remplies
If Tvide = 16711680 Then T = 0: GoTo suite2
TNb_car = Application.WorksheetFunction.CountIf(myrange, "*")
tval_sup_0 = Application.WorksheetFunction.CountIf(myrange, ">=0") ': MsgBox tval_sup_0 'CountA=nbval Sum=somme:msgbox tval_sup_0
If tval_sup_0 > 1 Then T = tval_sup_0: Workbooks(NW1).Activate: Sheets(i).Select: Selection.SpecialCells(xlCellTypeConstants, 1).Select: Tsum = Application.WorksheetFunction.Sum(Selection): ThisWorkbook.Activate: Cells(i + FirstLig, 4) = Tsum
suite2:
Cells(i + FirstLig, 1) = NW1S(i): Cells(i + FirstLig, 2) = TCelNonvide: If tval_sup_0 > 0 Then Cells(i + FirstLig, 3) = T
Next
'Boucle Feuilles sur fichier 2
For i = 1 To Workbooks(NW2).Worksheets.Count
NW2S(i) = Workbooks(NW2).Worksheets(i).Name
Set myrange = Workbooks(NW2).Worksheets(i).Range("A1:IU65536") '.SpecialCells(xlCellTypeFormulas, xlNumbers)
Tvide = Application.WorksheetFunction.CountBlank(myrange) 'compte vides
TCelNonvide = Application.WorksheetFunction.CountA(myrange) 'compte Nombre de cellules remplies
If Tvide = 16711680 Then T = 0: GoTo suite3
TNb_car = Application.WorksheetFunction.CountIf(myrange, "*")
tval_sup_0 = Application.WorksheetFunction.CountIf(myrange, ">=0") ': MsgBox tval_sup_0 'CountA=nbval Sum=somme:msgbox tval_sup_0
If tval_sup_0 > 1 Then T = tval_sup_0: Workbooks(NW2).Activate: Sheets(i).Select: Selection.SpecialCells(xlCellTypeConstants, 1).Select: Tsum = Application.WorksheetFunction.Sum(Selection): ThisWorkbook.Activate: Cells(i + FirstLig, 8) = Tsum
suite3:
Cells(i + FirstLig, 5) = NW2S(i): Cells(i + FirstLig, 6) = TCelNonvide: If tval_sup_0 > 0 Then Cells(i + FirstLig, 7) = T
Next
MsgBox Timer - t1
End Sub