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
|
Public Sub aliment()
Dim vierge_Marseille As Workbook, vierge_Paris As Workbook, EPI As Workbook
Dim Anc_Paris As Worksheet, Anc_Marseille As Worksheet
Workbooks.Open ("...CSV"), local:=True
Set EPI = Workbooks("....CSV")
Set origine = EPI.Worksheets("...")
Workbooks.Open (".....xls"), local:=True
Set vierge_Marseille = Workbooks("...xls")
Set Anc_Marseille = vierge_Marseille .Worksheets("ANCIENNE OFFRE")
Workbooks.Open ("...xls"), local:=True
Set vierge_Paris = Workbooks("....xls")
Set Anc_Paris = vierge_Paris.Worksheets("ANCIENNE OFFRE")
origine.Activate
NbligneTot = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To NbligneTot
origine.Activate
cellule = Range("A" & i).Activate
If ActiveCell.Find(What:="MARSEILLE", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then
ligne = ActiveCell.Row
colonne = ActiveCell.Column
Range("A" & ligne & ":W" & ligne).Select
Selection.Copy
Anc_Marseille.Activate
debut = Range("B" & Rows.Count).End(xlUp).Row + 1
Range("B" & debut).Select
'copier coller en valeur
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ElseIf ActiveCell.Find(What:="PARIS", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate Then
ligne = ActiveCell.Row
colonne = ActiveCell.Column
Range("A" & ligne & ":W" & ligne).Select
Selection.Copy
Anc_Paris.Activate
debut = Range("B" & Rows.Count).End(xlUp).Row + 1
Range("B" & debut).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
Next
End Sub |
Partager