Filtre données copier-coller par VBA

Option Explicit
Private Sub Worksheet_Activate()
Dim vFEuille As Worksheet
ComboBox1.Clear
 
    For Each vFEuille In ActiveWorkbook.Sheets
            ComboBox1.AddItem vFEuille.Name
    Next
End Sub
Private Sub ComboBox1_Click()
Dim OngletBassin As String
    OngletBassin = ComboBox1.Value
    ComboBox2.ListFillRange = OngletBassin + "!A2:A4"
End Sub
 
Private Sub ComboBox2_Click()
Dim OngletBassin As String
Dim Rome As String
Dim Cell As Range
Dim CellCible As Range
Dim C As Byte
Set CellCible = ActiveSheet.Range("C45")
OngletBassin = ComboBox1.Value
Rome = ComboBox2.Value

For Each Cell In Sheets(OngletBassin).Range("A2:A4")
    If Cell = Rome Then
         For C = 0 To 30
            CellCible.Offset(0, C) = Cell.Offset(0, C)
         Exit For
         Next
    End If
Next Cell
End Sub

Option Explicit
Private Sub Worksheet_Activate()
Dim vFEuille As Worksheet
ComboBox1.Clear
 
    For Each vFEuille In ActiveWorkbook.Sheets
        If Not vFEuille.Name = ActiveSheet.Name Then
                ComboBox1.AddItem vFEuille.Name
        End If
    Next
End Sub
Private Sub ComboBox1_Click()
Dim OngletBassin As String
Dim PlageSource As Range
OngletBassin = ComboBox1.Value
With Sheets(OngletBassin)
    Set PlageSource = .Range(.Range("A2"), .Range("A1000").End(xlUp))
End With
    
    ComboBox2.ListFillRange = OngletBassin & "!" & PlageSource.Address(0, 0)
End Sub
 
Private Sub ComboBox2_Click()
Dim OngletBassin As String
Dim Rome As String
Dim PlageSource As Range, CellSource As Range
Dim CellCible As Range
Dim C As Byte
If ComboBox1.ListIndex = -1 Then Exit Sub
Set CellCible = ActiveSheet.Range("C45")
OngletBassin = ComboBox1.Value
Rome = ComboBox2.Value
With Sheets(OngletBassin)
    Set PlageSource = .Range(.Range("A2"), .Range("A1000").End(xlUp))
End With
For Each CellSource In PlageSource
    If CellSource = Rome Then
         For C = 0 To 30
            CellCible.Offset(0, C) = CellSource.Offset(0, C)
         Next
        Exit For
    End If
Next CellSource
End Sub

Private Sub ComboBox2_Click()
Dim OngletBassin As String
Dim Rome As String
Dim PlageSource As Range, CellSource As Range
Dim CellCible As Range
Dim C As Byte
 
[COLOR=green]'Si pas de sélection en ComboBox1 on Sort de la Macro[/COLOR]
If ComboBox1.ListIndex = -1 Then Exit Sub
 
[COLOR=green]'DEFINITION de la Première Cellule Cible
[/COLOR]Set CellCible = ActiveSheet.Range("C45")
 
OngletBassin = ComboBox1.Value
Rome = ComboBox2.Value
 
With Sheets(OngletBassin)
[COLOR=green]'DEFINITION DE LA PLAGE DYNAMIQUE[/COLOR]
    Set PlageSource = .Range(.Range("A2"), .Range("A1000").End(xlUp))
End With
 
 
[COLOR=green]'BOUCLE sur chaque cellule de la Plage Dynamique
[/COLOR]For Each CellSource In PlageSource

    [COLOR=green]'Si la Cellule = La Valeur recherchée...
[/COLOR]    If CellSource = Rome Then

         [COLOR=green]'Pour 31 Colonnes (de A2 à AE5)[/COLOR]
         For C = 0 To 30

           [COLOR=green] 'on envoie la valeur des cellules avec le décalage (Offset)
[/COLOR]            CellCible.Offset(0, C) = CellSource.Offset(0, C)
         Next

        [COLOR=green] 'Une fois qu'on a trouvé la PREMIERE valeur on sort de la boucle...
[/COLOR]        Exit For
    End If

Next CellSource[COLOR=green] 'On Continue si on a pas trouvé...
[/COLOR]End Sub