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
| Sub CreerFeuilleDeSynthese()
' Cette macro assume :
' - Option Base 0
' - Travail dans le classeur actif
Dim wksSynth As Worksheet
Dim i As Long
Dim lSheet As Long
Dim wks As Worksheet
Dim rng As Range
Dim strAdressesCellules As Variant
Dim strSheetNames As Variant
' Remplir les tableaux des valeurs souhaitées pour les adresses de
' cellules et les noms de feuilles à traiter
strAdressesCellules = Array("$A$1", "$B$3", "$C$4", "$A$2")
strSheetNames = Array("Feuil1", "Feuil2", "Feuil3", "Feuil4", _
"Feuil5", "Feuil6", "Feuil7", "Feuil8")
' Créer une nouvelle feuille pour la synthèse
Set wksSynth = Worksheets.Add(After:=Worksheets(Worksheets.Count))
' Pour toutes les feuilles du classeur
For lSheet = 0 To UBound(strSheetNames)
Set wks = Worksheets(strSheetNames(lSheet))
With wksSynth.UsedRange
If .Cells.Count = 1 Then
Set rng = wksSynth.UsedRange
Else
Set rng = .Cells(.Rows.Count + 1, 1)
End If
End With
rng.Value = wks.Name
For i = 0 To UBound(strAdressesCellules)
rng.Offset(0, i + 1).Value = _
wks.Range(strAdressesCellules(i)).Value
Next
Next lSheet
Set wks = Nothing
Set wksSynth = Nothing
Set rng = Nothing
End Sub |
Partager