excel - VBA loop through folder (mac) -

i'm new vba, i'm trying.

in excel mac 2011 workbook named "datafile.xlsm" (which stored on shared network drive), need macro clears current contents of sheet 1 (named "data"), loops through identified folder on shared network drive (& subfolders) find excel workbook name starting "info sheet". each such workbook, need copy sheet 1 c2:c12, transpose cells, , paste them sheet 1 (named "data") of "datafile" in new row, columns a:k.

i have messed around dir , macid stuff can't figure out. result, can work @ worksheet level (all data inside 1 workbook) need work @ workbook level (all data inside 1 folder).

please help. i've spent many many hours on , gotten nowhere.

here have works @ worksheet level:

sub copy_studentsheets_to_datasheet_withclearing()  dim ws worksheet  'clear contents target sheet sheets("data").select range("a3:k600").clearcontents range("a3:k600").clearformats  'copy specified range each worksheet (other "data" worksheet)     , paste , transpose new row in data worksheet each ws in activeworkbook.worksheets     if ws.name = "data"     'do nothing     else         ws.select         range("c2:c12").select         selection.copy         sheets("data").select         range("a1").select         selection.end(xldown).select         activecell.offset(1, 0).range("a1:k1").select         selection.pastespecial paste:=xlall, operation:=xlnone, skipblanks:=false, transpose:=true     end if next ws  activeworkbook.save   end sub