excel - Copy cell J1 from multiple files and paste into column of master file -

i have code take files folder, open 1 each one, print name first column of "master file" close , loop through entire folder way.

in each file opened, there information in cell j1 copy , paste column 3 of "master file". section of code returns error (object not support property or method cannot tell line referring to) , causes program stop after opening 1 file.

any ideas?

full code:

sub loopthroughdirectory()      dim objfso object     dim objfolder object     dim objfile object     dim myfolder string     dim sht worksheet     dim integer      myfolder = "c:\users\trembos\documents\tds\progress\"  set sht = activesheet      'create instance of filesystemobject     set objfso = createobject("scripting.filesystemobject")     'get folder object     set objfolder = objfso.getfolder(myfolder)     = 1     'loop through directory file , print names     each objfile in objfolder.files          if lcase(right(objfile.name, 3)) <> "xls" , lcase(left(right(objfile.name, 4), 3)) <> "xls"         else             'print file name             sht.cells(i + 1, 1) = objfile.name             = + 1             workbooks.open filename:=myfolder & objfile.name         end if         'get tds name of open file         range("j1").select         selection.copy         windows("masterfile.xlsm").activate         range("c2").select         activesheet.paste         objfile.activate         activeworkbook.close     next objfile   end sub 

part of code messing program:

'get tds name of open file range("j1").select selection.copy windows("masterfile.xlsm").activate range("c2").select activesheet.paste objfile.activate 

objfile.activate issue.

objfile not workbook variable, it's being assigned path\filename objfolder.files.

use following:

dim newworkbook workbook set newworkbook = workbooks.open filename:=myfolder & objfile.name . . . newworkbook.activate activeworkbook.close 

now, instead of last 2 lines, since have variable that's referencing opened workbook, can replace 2 lines one:

newworkbook.close 

read this link advice on other ways eliminate activate, select, etc make code cleaner, more readable, less have bugs due wrong place having focus , easier maintain.