Re : Copie de plage de cellules (variables) en VBA
Bonjour le forum,
avec cette macro légèrement retravaillé, j'obtiens le résultat souhaité.
elle rame un peu en exécution mais bon.
Sub Recopie()
'Thierry's Demo sur
Excel Downloads - Accueil, December 2002
'adaptée pour mes besoins
Dim cell As Range
Dim MaPlage As Range
Dim i As Integer
Dim iF2 As Integer
Dim ZoneA() As String, ZoneB() As String, ZoneC() As String, ZoneD() As String, ZoneE() As String
Dim ZoneF() As String, ZoneG() As String, ZoneH() As String, ZoneI() As String
Filtre = InputBox("Filtrez un mois !")
Sheets("Ventes(2)").Select
Sheets("Ventes(2)").Range("L1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Filtre
iF2 = 2
Set MaPlage = Vente.Range("A2", Range("A200").End(xlUp).Address)
Set MaPlage = MaPlage.SpecialCells(xlCellTypeVisible)
ReDim ZoneA(0 To MaPlage.Count - 1)
ReDim ZoneB(0 To MaPlage.Count - 1)
ReDim ZoneC(0 To MaPlage.Count - 1)
ReDim ZoneD(0 To MaPlage.Count - 1)
ReDim ZoneE(0 To MaPlage.Count - 1)
ReDim ZoneF(0 To MaPlage.Count - 1)
ReDim ZoneG(0 To MaPlage.Count - 1)
ReDim ZoneH(0 To MaPlage.Count - 1)
ReDim ZoneI(0 To MaPlage.Count - 1)
For Each cell In MaPlage
ZoneA(i) = cell.Value
Test.Range("A" & iF2) = ZoneA(i)
ZoneB(i) = cell.Offset(0, 1)
Test.Range("B" & iF2) = ZoneB(i)
ZoneC(i) = cell.Offset(0, 2)
Test.Range("C" & iF2) = ZoneC(i)
ZoneD(i) = cell.Offset(0, 3)
Test.Range("D" & iF2) = ZoneD(i)
ZoneE(i) = cell.Offset(0, 4)
Test.Range("E" & iF2) = ZoneE(i)
ZoneF(i) = cell.Offset(0, 5)
Test.Range("F" & iF2) = ZoneF(i)
ZoneG(i) = cell.Offset(0, 6)
Test.Range("G" & iF2) = ZoneG(i)
ZoneH(i) = cell.Offset(0, 7)
Test.Range("H" & iF2) = ZoneH(i)
ZoneI(i) = cell.Offset(0, 8)
Test.Range("I" & iF2) = ZoneI(i)
i = i + 1
iF2 = iF2 + 1
Next cell
End Sub
bonne journée.