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
|
Sub generer_fichier()
'
' generer_pages Macro
' Génère les 45 pages du classeur.
'
Const Accents As String = "àâäåçéèêëîïôöùûüÈÉÊËÀÁÂÃÄÅÙÚÛÜ- ,"
Const Normaux As String = "aaaaceeeeiioouuuEEEEAAAAAAUUUU___"
Dim c As Range, Derligne As Integer, i As Byte
Sheets("Menu").Select
Derligne = Range("A65536").End(xlUp).Row
For Each c In Range("A2:A" & Derligne)
For i = 1 To Len(Accents)
c.Value = Replace(c.Value, Mid(Accents, i, 1), Mid(Normaux, i, 1))
Next i
Next c
'
Sheets("Menu").Select
' Déterminer combien d'agent sur la feuille Menu
FinalAgent = Range("A65000").End(xlUp).Row
' Loop pour chaque agent
For x = 2 To FinalAgent
Sheets("Menu").Select
ThisAgent = Range("A" & x).Value
Dim wbk As Workbook
Application.ScreenUpdating = False
ThisWorkbook.Sheets(Array("Janvier", "Admin_Janvier", "Fevrier", "Admin_Fevrier", "Mars", "Admin_Mars", "Avril", "Admin_Avril", "Mai", "Admin_Mai", "Juin", "Admin_Juin", "Juillet", "Admin_Juillet", "Aout", "Admin_Aout", "Septembre", "Admin_Septembre", "Octobre", "Admin_Octobre", "Novembre", "Admin_Novembre", "Decembre", "Admin_Decembre", "AGT", "SGT")).Copy
Set wbk = ActiveWorkbook
Application.DisplayAlerts = False
wbk.SaveAs DossierSauvegarde & "\" & ThisAgent & ".xlsx"
Application.DisplayAlerts = True
wbk.Close
Set wbk = Nothing
Next x
Sheets("Menu").Select
MsgBox ("Opération terminée.")
End Sub |
Partager