P: 55
|
Below you will find the code that I have written. I am asking it to search for an Excel file. Once it finds the correct dated Excel file it opens it. From there it is in search of information. I can't determine if the macro is working without fixing the following date - 1 issue. Any assistance would be greatly appreciated. I am using Excel 2003. - Dim FileDate, Cusip, Here, Today, There
-
FileDate = InputBox("Enter File Date in yyyymmdd format")
-
-
Set Today = Nothing
-
Set Today = Date - 1
-
-
Set Cusip = Nothing
-
Set Here = Nothing
-
Set There = Nothing
-
-
Workbooks.Open ("S:\fixed_income_etfs\outgoing\Archive\Trade Date " & FileDate & "\Fund_Performance_Report_" & FileDate & ".csv")
-
Windows("ETFPerfComp (Test)+.xls").Activate
-
Range("E3").Select
-
Set Cusip = ActiveCell
-
Windows("Fund_Performance_Report_" & FileDate & ".csv").Activate
-
Cells.Find(What:=Cusip, After:=ActiveCell, LookIn:=xlFormulas, _
-
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
-
MatchCase:=False, SearchFormat:=False).Activate
-
ActiveCell.Offset(0, 1).Select
-
Set Here = ActiveCell
-
Selection.Copy
-
Windows("ETFPerfComp (test)+.xls").Activate
-
-
Cells.Find(What:=CurrentDate, After:=ActiveCell, LookIn:=xlFormulas, _
-
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
-
MatchCase:=False, SearchFormat:=False).Activate
-
Set There = Active.Cell
-
ActiveCell.Offset(-1, 10).Select
-
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
-
:=False, Transpose:=False
-
-
Windows("Fund_Performance_Report_" & FileDate & ".csv").Activate
-
ActiveCell.Offset(0, 1).Select
-
Set Here = ActiveCell
-
Selection.Copy
-
-
Windows("ETFPerfComp (test)+.xls").Activate
-
Range(There).Select
-
ActiveCell.Offset(-1, 4).Select
-
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
-
:=False, Transpose:=False
-
-
Windows("Fund_Performance_Report_" & FileDate & ".csv").Activate
-
ActiveCell.Offset(0, 1).Select
-
Set Here = ActiveCell
-
Selection.Copy
-
Windows("ETFPerfComp (test)+.xls").Activate
-
Range(There).Select
-
ActiveCell.Offset(0, 11).Select
-
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
-
:=False, Transpose:=False
-
Windows("Fund_Performance_Report_" & FileDate & ".csv").Activate
-
ActiveCell.Offset(0, 1).Select
-
Set Here = ActiveCell
-
Selection.Copy
-
Windows("ETFPerfComp (test)+.xls").Activate
-
Range(There).Select
-
ActiveCell.Offset(0, 5).Select
-
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
-
:=False, Transpose:=False
| |