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 56 57 58 59 60
| Sub copy_tech()
Workbooks.Open Filename:="T:\Arnaud Test VBA\Test World Invest\0071006.CSV", local:=True
Application.ScreenUpdating = False
Dim FL1 As Worksheet
Dim FL2 As Worksheet
Dim cond1 As String
Dim cond2 As String
Dim L As Range
Set FL1 = Workbooks("0071006.CSV").Worksheets("0071006")
Set FL2 = Workbooks("PRICE CHECK - WI.xls").Worksheets("TECH")
'Define the condition to search for
cond1 = "ACTION "
cond2 = "OBLIGATION "
'Loop to search for the Equities & Bonds to import in the Price Check file
For J = 1 To 300
d = J + 13
Workbooks("0071006.CSV").Activate
If Sheets("0071006").Cells(J, 5).Value = cond1 Or Sheets("0071006").Cells(J, 5).Value = cond2 Then
Workbooks("0071006.CSV").Activate
FL1.Cells(J, 8).Select
Selection.Copy
Workbooks("PRICE CHECK - WI.xls").Activate
ActiveSheet.Paste Destination:=FL2.Cells(d, 1)
Workbooks("0071006.CSV").Activate
FL1.Cells(J, 7).Select
Selection.Copy
Workbooks("PRICE CHECK - WI.xls").Activate
ActiveSheet.Paste Destination:=FL2.Cells(d, 2)
Workbooks("0071006.CSV").Activate
FL1.Cells(J, 14).Select
Selection.Copy
Workbooks("PRICE CHECK - WI.xls").Activate
ActiveSheet.Paste Destination:=FL2.Cells(d, 3)
End If
Next J
'Closing of the 0071006.CSV Workbook
Workbooks("PRICE CHECK - WI.xls").Activate
Workbooks("0071006.CSV").Close
'Replacement of the spaces by nothing
'Workbooks("PRICE CHECK - WI.xls").Worksheets("TECH")
FL2.Range("B14:B1000").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False |
Partager