excel - Pull latest workbook copy selected workbook and paste in master workbook -

i trying in folder pull latest workbook date, open workbook src data, copy selected worksheet , data src , paste master workbook. closing src workbook without saving change. i'm having issues on should place file paths , filenames.

function newestfilename(byval path string, byval filetemplate string) string  dim filedatecrnt date dim filedatenewest date dim filenamecrnt string dim filenamenewest string  if right("g:\aoc\groups1\sac\test", 1) <> "\" path = "g:\aoc\groups1\sac\test" & "\" end if  filenamecrnt = dir$("g:\aoc\groups1\sac\test" & book1.xlsx) if filenamecrnt = "book1.xlsx" newestfilename = "book2.xlsx" exit function end if  filenamenewest = filenamecrnt filedatenewest = filedatetime("g:\aoc\groups1\sac\test" & filenamecrnt) while true filenamecrnt = dir$ if filenamecrnt = "" exit filedatecrnt = filedatetime(path & filenamecrnt) if filedatecrnt > filedatenewest   filenamenewest = filenamecrnt   filedatenewest = filedatecrnt end if loop  newestfilename = filenamenewest  call readdatafromclosefile  end function  sub readdatafromclosefile() on error goto errhandler application.screenupdating = false  dim src workbook  set src = workbook.open("g:\aoc\groups1\sac\test.xlsx", true, true)  dim itotalrows integer itotalrows = src.worksheets("sheet1").range("b1:b" & cells(rows.count, "b").end(xlup).row)  dim icnt integer icnt = 1 itotalrows worksheets("sheet1").range("b" & icnt).formula = src.worksheets("sheet1").range("b" & icnt).formula next icnt  src.close false set scr = nothing  errhandler: application.enableevents = true application.screenupdating = true end sub 

first things first:

if have question or encounter error, spell out. it's hard find out error is, without knowing on line occurs.

your function in whole doesn't make sense. taking @ it, commenting would've been helpful.

let's go through code step step:

if right("g:\aoc\groups1\sac\test", 1) <> "\" path = "g:\aoc\groups1\sac\test" & "\" end if 

this if-condition trigger, because string put in there, same , it'll miss "\". if path doesn't change can change path = "g:\aoc\groups1\sac\test\"

---

filenamecrnt = dir$("g:\aoc\groups1\sac\test" & book1.xlsx) if filenamecrnt = "book1.xlsx" newestfilename = "book2.xlsx" exit function end if 

i'm not sure trying here. setting filenamecrnt string in first line (you missing "\" btw). guess "book1.xlsx" real name of workbook, string should either this: "g:\aoc\groups1\sac\test\book1.xlsx" or this

filename = "book1.xlsx" filenamecrnt = dir$("g:\aoc\groups1\sac\test" & filename ) 

next: would(!) exit function there, if line above work. set filenamecrnt book1.xlsx, check via if-clause, check return true, afterwards you'd exit.

---

i idea of loop, broken. start changing this: if filenamecrnt = "" exit do else. variable never empty loop cause runtime error. start changing first parts of function , later. think have better idea how should work. , it's better try solving things yourself. ;)

edit:

it's helpful make flow chart on how program should run. like:

• get current filename
• get date of current file
• check if there newer file (a file higher date old date)
• get dates of all files (loop through files)
• get highest date
• compare highest date date of current file
• if there file higher date, update current filename filename higher date

hth