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
| Option Explicit
Private ShSynthese As Worksheet
Private ShParametres As Worksheet
Private AireACopier As Range
Private FeuillesACopier As Range
Private CelluleFeuilles As Range
Private AdresseDonnees As String
Private LigneDeTitreSynthese As Long
Private DerniereLigneSynthese As Long
Private PremiereColonneSynthese As Long
Private LigneDebutSynthese As Long
Private ColonneFeuillesAIncorporer As Long
Private LigneDeTitreParametres As Long
Private DerniereLigneParametres As Long
Sub CopierLesDonneesDansSynthese()
Application.ScreenUpdating = False
Set ShSynthese = Sheets("Synthèse")
LigneDeTitreSynthese = 10
PremiereColonneSynthese = 2
' Effacement de la feuille synthèse
ShSynthese.Range(ShSynthese.Cells(LigneDeTitreSynthese + 1, 1), ShSynthese.Cells(ShSynthese.Rows.Count, ShSynthese.Columns.Count)).ClearContents
DerniereLigneSynthese = ShSynthese.Cells(ShSynthese.Rows.Count, PremiereColonneSynthese).End(xlUp).Row
Set ShParametres = Sheets("Paramètres")
' Chargement de la liste des feuilles à incorporer et de l'adresse de l'aire à copier
AdresseDonnees = ShParametres.Range("AdresseDonneesACopier")
LigneDeTitreParametres = 10
ColonneFeuillesAIncorporer = 1
DerniereLigneParametres = ShParametres.Cells(ShParametres.Rows.Count, ColonneFeuillesAIncorporer).End(xlUp).Row
Set FeuillesACopier = ShParametres.Range(ShParametres.Cells(LigneDeTitreParametres + 1, ColonneFeuillesAIncorporer), ShParametres.Cells(DerniereLigneParametres, ColonneFeuillesAIncorporer))
For Each CelluleFeuilles In FeuillesACopier
Set AireACopier = Sheets(CelluleFeuilles.Value).Range(ShParametres.Range("AdresseDonneesACopier"))
With ShSynthese
LigneDebutSynthese = DerniereLigneSynthese
AireACopier.Copy
.Cells(DerniereLigneSynthese + 1, PremiereColonneSynthese).Select
ShSynthese.Paste
DerniereLigneSynthese = ShSynthese.Cells(ShSynthese.Rows.Count, PremiereColonneSynthese).End(xlUp).Row
.Range(ShSynthese.Cells(LigneDebutSynthese + 1, 1), ShSynthese.Cells(DerniereLigneSynthese, 1)) = CelluleFeuilles
End With
Set AireACopier = Nothing
Next CelluleFeuilles
ShSynthese.Cells(LigneDeTitreSynthese, 1).Activate
Set ShParametres = Nothing
Set ShSynthese = Nothing
Application.ScreenUpdating = True
End Sub |
Partager