i found the problem. it is becuase the source workbook file format is .xlsb and once i changed it to .xls then it worked.

all of my source workbooks are originally xlsb is there anyway that the code will work in xlsb source workbook?