Re : excel comparer colonnes
Pour que la macro (excellente soit dit en passant) de Catrice tourne sur tous les onglets, j'ai fait ça :
Sub Test()
Source = "donnees"
Dest = "clients"
For Each X In Sheets(Source).Range("A2:" & Sheets(Source).Range("A65536").End(xlUp).Address)
With Sheets(Dest).Range("A2:" & Sheets(Dest).Range("A65536").End(xlUp).Address)
Set c = .Find(X.Value)
If c Is Nothing Then
Sheets(Source).Range(Sheets(Source).Cells(X.Row, 1), Sheets(Source).Cells(X.Row, 256).End(xlToLeft)).Copy Sheets(Dest).Range("A65536").End(xlUp).Offset(1, 0)
Else
Marqueur = 0
firstAddress = c.Address
Do
If X.Offset(0, 1) = c.Offset(0, 1) Then Marqueur = 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
If Marqueur = 0 Then Sheets(Source).Range(Sheets(Source).Cells(X.Row, 1), Sheets(Source).Cells(X.Row, 256).End(xlToLeft)).Copy Sheets(Dest).Range("A65536").End(xlUp).Offset(1, 0)
End If
End With
Next
Sheets(Dest).Range("b1").Sort Key1:=Sheets(Dest).Range("b2"), Order1:=xlAscending, Header:=xlYes
Dest = "TVA"
For Each X In Sheets(Source).Range("A2:" & Sheets(Source).Range("A65536").End(xlUp).Address)
With Sheets(Dest).Range("A2:" & Sheets(Dest).Range("A65536").End(xlUp).Address)
Set c = .Find(X.Value)
If c Is Nothing Then
Sheets(Source).Range(Sheets(Source).Cells(X.Row, 1), Sheets(Source).Cells(X.Row, 256).End(xlToLeft)).Copy Sheets(Dest).Range("A65536").End(xlUp).Offset(1, 0)
Else
Marqueur = 0
firstAddress = c.Address
Do
If X.Offset(0, 1) = c.Offset(0, 1) Then Marqueur = 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
If Marqueur = 0 Then Sheets(Source).Range(Sheets(Source).Cells(X.Row, 1), Sheets(Source).Cells(X.Row, 256).End(xlToLeft)).Copy Sheets(Dest).Range("A65536").End(xlUp).Offset(1, 0)
End If
End With
Next
Sheets(Dest).Range("b1").Sort Key1:=Sheets(Dest).Range("b2"), Order1:=xlAscending, Header:=xlYes
Dest = "Fiscal"
For Each X In Sheets(Source).Range("A2:" & Sheets(Source).Range("A65536").End(xlUp).Address)
With Sheets(Dest).Range("A2:" & Sheets(Dest).Range("A65536").End(xlUp).Address)
Set c = .Find(X.Value)
If c Is Nothing Then
Sheets(Source).Range(Sheets(Source).Cells(X.Row, 1), Sheets(Source).Cells(X.Row, 256).End(xlToLeft)).Copy Sheets(Dest).Range("A65536").End(xlUp).Offset(1, 0)
Else
Marqueur = 0
firstAddress = c.Address
Do
If X.Offset(0, 1) = c.Offset(0, 1) Then Marqueur = 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
If Marqueur = 0 Then Sheets(Source).Range(Sheets(Source).Cells(X.Row, 1), Sheets(Source).Cells(X.Row, 256).End(xlToLeft)).Copy Sheets(Dest).Range("A65536").End(xlUp).Offset(1, 0)
End If
End With
Next
Sheets(Dest).Range("b1").Sort Key1:=Sheets(Dest).Range("b2"), Order1:=xlAscending, Header:=xlYes
End Sub
ça marche nickel mais il y a peut être plus simple
Par contre ça me reprend toute la ligne pour les nouveaux clients et non pas seulements les colonnes dont j'ai besoin.
Une solution?