excel vba - Transfer data from specific sheet of multiple files to master file -

can me on following: have 12 workbooks in folder "fiscal year 11-12". 12 files have common sheet name "categorized". i'm trying transfer data sheet of files file yearlyexpense.xlsm, following error:

run-time error 1004. "xxx.xlsx" not found. check spelling of name, , verify file location correct.

my code follows:

sub loopthroughdirectory()  dim myfile string dim erow myfile = dir("c:\users\winston\documents\family budget\fiscal year 11-12\")  while len(myfile) > 0     if myfile = "yearlyexpense.xlsm"         exit sub     end if      workbooks.open (myfile)        'this i'm getting error 1004 vba      sheets("categorized").select     range("b32:v32").copy     activeworkbook.close      erow = sheet2.cells(rows.count, 1).end(xlup).offset(1, 0).row     activesheet.paste destination:=worksheets("sheet2").range(cells(erow, 1), cells(erow, 22))      myfile = dir loop 

end sub

you need express full path and directory listing file mask on initial call dir. return filename; need concatenate path folder file resides in. after processing first file, need call dir again next file in folder complies original file mask.

sub loopthroughdirectory()      dim myfile string, mypath string, wb workbook      mypath = "c:\users\winston\documents\family budget\fiscal year 11-12\"     myfile = dir(mypath & "\*.xl*")      while len(myfile) > 0         if lcase(myfile) <> "yearlyexpense.xlsm"              debug.print mypath & chr(92) & myfile  'use open workbook             set wb = workbooks.open(mypath & chr(92) & myfile)             wb.sheets("categorized")                 'i'll admit confusion here gets copied ,                 .range("b32:v32").copy _                     destination:=thisworkbook.sheet2.cells(rows.count, 1).end(xlup).offset(1, 0)             end             wb.close false             set wb = nothing         end if         myfile = dir     loop  end sub