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
| Sub Archiver()
Dim wbk As Workbook
Dim aSh As Worksheet, Sh As Worksheet
Dim iLRA As Long, iLRN As Long, i As Long, j As Long
Dim Dossier As String, Fichier As String, Feuille As String, DossierNew As String
Dim k As Byte, s As Byte
DossierNew = "D:\Nouveau dossier\Archive Feuille\"
Dossier = "C:\Documents and Settings\Personnel\Bureau\Archive Feuille Immob\"
Application.ScreenUpdating = False
Set aSh = ThisWorkbook.Worksheets(2)
If IsDate(aSh.Range("A2").Value) Then
Fichier = Dossier & Year(aSh.Range("A2").Value) & ".xls"
Feuille = MonthName(Month(aSh.Range("A2").Value))
On Error Resume Next
Set wbk = Workbooks.Open(Fichier)
On Error GoTo 0
If wbk Is Nothing Then
Set wbk = Workbooks.Add(1)
Set Sh = wbk.Worksheets(1)
Sh.Name = Feuille
Else
On Error Resume Next
Set Sh = Worksheets(Feuille)
On Error GoTo 0
If Sh Is Nothing Then
Set Sh = wbk.Worksheets.Add(After:=wbk.Sheets(wbk.Sheets.Count))
Sh.Name = Feuille
End If
End If
iLRA = Sh.Cells(Sh.Rows.Count, 3).End(xlUp).Row
iLRN = aSh.Cells(aSh.Rows.Count, 3).End(xlUp).Row
If Sh.Range("A1") = "" Then
aSh.Range("A1:F" & iLRN).SpecialCells(xlCellTypeVisible).Copy Sh.Range("A" & iLRA)
Else
aSh.Range("A2:F" & iLRN).SpecialCells(xlCellTypeVisible).Copy Sh.Range("A" & iLRA + 1)
End If
With Sh
iLRA = .Cells(.Rows.Count, 3).End(xlUp).Row
For i = 2 To iLRA - 1
For j = iLRA To i + 1 Step -1
For k = 1 To 6
If .Cells(i, k) = .Cells(j, k) Then
s = s + 1
Else
Exit For
End If
Next k
If s = 6 Then .Rows(j).Delete
s = 0
Next j
Next i
.Columns("A:A").ColumnWidth = 18
.Columns("B:B").ColumnWidth = 10.71
.Columns("C:C").ColumnWidth = 8
.Columns("D:D").ColumnWidth = 3
.Columns("E:E").ColumnWidth = 51
.Columns("F:F").ColumnWidth = 10.71
End With
ChDir DossierNew
Application.DisplayAlerts = False
wbk.SaveAs Year(aSh.Range("A2").Value) & ".xls"
Application.DisplayAlerts = True
wbk.Close
Set wbk = Nothing
Set Sh = Nothing
End If
Set aSh = Nothing
End Sub |
Partager