jsauriol
11-28-2007, 10:20 AM
The formula below opens a excel report that has a date on it. We run that report every couple of days so the date changes constantly. I have a macro that updates values based on new information from the other report i just spoke of. I cant seem to think of a way to code this so it just looks for the most current file. Here is what I have:
Application.ScreenUpdating = False
ChDir "K:\Procurement\PO DATA"
PODATAPath = Application.GetOpenFilename("(*.xls),*.xls", , "PO Data as of...")
Workbooks.Open Filename:=PODATAPath
Windows("PO Data as of 11-27-07.xls").Activate
Windows("Shipping Method Macro.xls").Activate
ActiveWindow.SmallScroll Down:=-20
Range("F7").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[PO Data as of 11-27-07.xls]MASTER'!C1:C20,20,0)"
Selection.AutoFill Destination:=Range("F7:F50"), Type:=xlFillDefault
Range("F7:F50").Select
PO Data is the file that I use to update the other Report I have called Shipping Method. Shipping Method is where this code comes from. As you can see I am currently taking from "PO Data as of 11-27-07" and I need something to just vlookup from the most current file called PO Data.
I hope this makes sense to someone of what I need. I hope you guys can help.
Application.ScreenUpdating = False
ChDir "K:\Procurement\PO DATA"
PODATAPath = Application.GetOpenFilename("(*.xls),*.xls", , "PO Data as of...")
Workbooks.Open Filename:=PODATAPath
Windows("PO Data as of 11-27-07.xls").Activate
Windows("Shipping Method Macro.xls").Activate
ActiveWindow.SmallScroll Down:=-20
Range("F7").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[PO Data as of 11-27-07.xls]MASTER'!C1:C20,20,0)"
Selection.AutoFill Destination:=Range("F7:F50"), Type:=xlFillDefault
Range("F7:F50").Select
PO Data is the file that I use to update the other Report I have called Shipping Method. Shipping Method is where this code comes from. As you can see I am currently taking from "PO Data as of 11-27-07" and I need something to just vlookup from the most current file called PO Data.
I hope this makes sense to someone of what I need. I hope you guys can help.