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
| Sub essai()
Dim DerSc As Long, WSource As Workbook, FSource As Worksheet
Dim derMaj As Long, WMaj As Workbook, FMaj As Worksheet, FC As String
Dim x As Long
Application.ScreenUpdating = False
Set WSource = Workbooks.Open(Filename:="C:\Users\ssaddi\Desktop\REQUETEGIR.xls")
Set FSource = WSource.Sheets("REQ")
Set FMaj = WMaj.Sheets("feuil1")
Set WMaj = Workbooks("fichier_maj.xls")
'si les ref sont en B dans les deux fichiers
DerSc = FSource.Range("B" & FSource.Rows.Count).End(xlUp).Row 'dernière ligne du fichier source
derMaj = FMaj.Range("B" & FMaj.Rows.Count).End(xlUp).Row 'dernière ligne du fichier source
'les données du fichier "maj" commencent en B2
'une colonne temporaire
FC = "=NB.SI(" & "'[" & WSource.Name & "]" & FSource.Name & "'!$B$2:$B$" & DerSc & ";B2)"
FMaj.Range("F2").FormulaLocal = FC
FMaj.Range("F2").AutoFill Destination:=FMaj.Range("F2:F" & derMaj), Type:=xlFillDefault
For x = derMaj To 2 Step -1
If FMaj.Range("F" & x) = 0 Then
FMaj.Range("F" & x).EntireRow.Delete
End If
Next x
'on supprime la colonne temporaire
FMaj.Columns("F:F").Delete
'on recommence mais à l'envers, pour ajouter les lignes
DerSc = FSource.Range("B" & FSource.Rows.Count).End(xlUp).Row
derMaj = FMaj.Range("B" & FMaj.Rows.Count).End(xlUp).Row
'une colonne temporaire, cette fois au fichier source
FC = "=NB.SI(" & "'[" & WMaj.Name & "]" & FMaj.Name & "'!$B$2:$B$" & derMaj & ";B2)"
FSource.Range("F2").FormulaLocal = FC
FSource.Range("F2").AutoFill Destination:=FSource.Range("F2:F" & DerSc), Type:=xlFillDefault
For x = Der_Sc To 2 Step -1
If F_Source.Range("F" & x) = 0 Then
F_Maj.Range("A" & derMaj + 1, "G" & derMaj + 1).Value = FSource.Range("A" & x, "G" & x).Value
derMaj = FMaj.Range("B" & FMaj.Rows.Count).End(xlUp).Row
End If
Next x
'on supprime la colonne temporaire
FSource.Columns("F:F").Delete
Application.ScreenUpdating = True
Application.Goto Reference:="Miseajour"
Application.WindowState = xlMinimized
Application.WindowState = xlMinimized
End Sub |
Partager