1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111
| Private Sub Boucle_recu_reponse()
' ------------------------------------------------------------------------------------------- '
' Ouverture des fichiers recu et reponse
' ------------------------------------------------------------------------------------------- '
Dim Win As Workbook
Dim W1 As Workbook, NomW1 As String, CheminW1 As String
Dim W2 As Workbook, NomW2 As String, cheminW2 As String
Dim boul2 As Boolean, boul1 As Boolean
NomW1 = "yyy.xls" 'Fichier réponse
NomW2 = "zzz.xls" ' Fichier reçu
CheminW1 = "x:\xxx\Dossier reponse"
cheminW2 = "x:\xxx\xxx\Dossier recu"
For Each Win In Workbooks
If Win.Name = NomW1 Then boul1 = True:
If Win.Name = NomW2 Then boul2 = True:
Next
If Not boul1 Then
Workbooks.Open Filename:=CheminW1 & Application.PathSeparator & NomW1
End If
If Not boul2 Then
Workbooks.Open Filename:=cheminW2 & Application.PathSeparator & NomW2
End If
Set W1 = Workbooks(NomW1)
Set W2 = Workbooks(NomW2)
' ------------------------------------------------------------------------------------------- '
Dim NumErr As Long
' Filtrer sur le numéro
Dim sHAno As String, sHres As String
Const LibErr As String = "Err"
sHAno = "www.ANO"
sHres = "Résultats "
sHerr = LibErr
' ------------------------------------------------------------------------------------------- '
' Si l'onglet des anos existe le supprimer, resultat aussi avant la boucle
' ------------------------------------------------------------------------------------------- '
Dim Max As Integer
For i = Sheets.Count To 1 Step -1
'Suppression des onglets recu et reponse
'Suppression des onglets erreurs
If Sheets(i).Name = sHAno Or Sheets(i).Name = sHres Or Mid(Sheets(i).Name, 1, 3) = Mid(sHerr, 1, 3) Then
Sheets(i).Delete
End If
Next
' ------------------------------------------------------------------------------------------- '
' Recopie de l'onglet du fichier dans ce classeur
' ------------------------------------------------------------------------------------------- '
Workbooks(NomW1).Sheets _
(sHAno).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
' Creation de l'onglet Résultat
Add_sheet sHres, True
' ------------------------------------------------------------------------------------------- '
' Boucle sur les numéros 1 à MAX
' ------------------------------------------------------------------------------------------- '
Max = 999
For NumErr = 1 To Max
Dim FA As Worksheet, FR As Worksheet, FE As Worksheet
Dim MaPlage As Range
sHerr = sHerr & NumErr
Set FR = Worksheets(sHres)
Set FA = Worksheets(sHAno)
Set MaPlage = FA.Range("P:P")
MaPlage.AutoFilter Field:=16, Criteria1:=CStr(NumErr)
TotErr = FA.AutoFilter.Range.Columns(16).SpecialCells(xlCellTypeVisible).Cells.Count
'On continue seulement si l'erreur n'est pas trouvée
If TotErr > 1 Then 'La ligne des titres seulement donne 1
'Creation de l'onglet erreur des reponses filtrées
sHerr = LibErr & NumErr
Add_sheet sHerr, True
Set FE = Worksheets(sHerr)
'Recopie des zones filtrées dans le nouvel onglet
FA.Cells.SpecialCells(xlCellTypeVisible).Copy
FE.Paste
FA.Range("A1").EntireRow.Copy
FE.Rows("1:1").PasteSpecial Paste:=8
FE.Rows("1:1").AutoFilter
End If
'Ne plus filtrer le fichier pour la boucle
MaPlage.AutoFilter Field:=16
Next
End Sub |
Partager