vba - errors pulling key information from multiple excel workbooks -

i attempting write macro in workbook purpose display key information each of set of excel files. first column contains name of file used in code.

the code have written far should loop through list of 11 file names in summary sheet , pull info called cell e21 in each of files , place in cell hx in summary sheet.

i have had no luck getting work far, first error im getting "invalid qualifier" on line says "mysheet". know there alot of other mistakes here have never attempted write sub pulls other closed workbooks.

my code follows:

option explicit  sub oeesummmary() dim gcell range dim txt$, mypath$, mywb$, mysheet$ dim myvalue integer      dim x long     dim v variant, r range, rwhere range  mypath = "l:\manufacturing engineering\samuel hatcher\" x = 2  mysheet = activesheet.name  application.screenupdating = false  while mysheet.range("a" & x).value <> ""      mywb = mysheet.range("a" & x).txt     workbooks.open filename:=mypath & mywb      set gcell = activesheet.range("e21")         thisworkbook.activesheet.range("a" & x)         .value = "item"         .offset(7, 0).value = gcell.value          end     activeworkbook.close savechanges:=false      x = x + 1  loop  end sub 

ive looked @ invalid qualifier error , dont understand have wrong part of code. , other blinding errors appreciated!

the issue see that's causing invalid qualifier error declaring mysheet string, trying use worksheet object. below i've declared worksheet , set activesheet. changed thisworkbook.activesheet reference mysheet, think want. changed txt text:

sub oeesummmary() dim gcell range dim mysheet worksheet dim txt$, mypath$, mywb$ dim myvalue integer dim x long dim v variant, r range, rwhere range  mypath = "l:\manufacturing engineering\samuel hatcher\" x = 2 set mysheet = activesheet  application.screenupdating = false  while mysheet.range("a" & x).value <> ""     mywb = mysheet.range("a" & x).text     workbooks.open filename:=mypath & mywb     set gcell = activesheet.range("e21")         mysheet.range("a" & x)             .value = "item"             .offset(7, 0).value = gcell.value         end     activeworkbook.close savechanges:=false     x = x + 1 loop  end sub