Option Explicit
Sub Bouton1_QuandClic()
Dim ws As Worksheet, feuille As Worksheet
Dim data As Collection
Dim i As Integer
Dim c As Range
Set data = New Collection
Set feuille = Sheets.Add
On Error GoTo fin:
With feuille
.Name = 'Bilan'
.Move before:=Sheets(1)
End With
For Each ws In Worksheets
If ws.Name <> feuille.Name Then
With ws
For Each c In .Range('a1:a' & .Range('a65536').End(xlUp).Row)
On Error Resume Next
data.Add CStr(c), CStr(c)
On Error GoTo 0
Next c
End With
End If
Next ws
With feuille
For i = 1 To data.Count
.Cells(i, 1) = data(i)
Next i
End With
Exit Sub
fin:
MsgBox 'la feuille Bilan existe déjà.'
With Application
.DisplayAlerts = False
ActiveSheet.Delete
.DisplayAlerts = True
End With
End Sub