excel vba - Loop through many workbooks, loop through only the first and second worksheets, then copy/paste cells into a workbook -

"new vba user, excel 2010, , have several cost estimate workbooks within same folder. within separate summary workbook want loop through workbooks, loop through first , second worksheets, copy , final paste values of specific cells.

i have pieced snippets several sources in below. first "if" loop worksheet "distro sheet" seems grabbing data. second "if" loop "execution estimate" never seems paste cells? have tried flagging first 2 worksheets, used array, , used "case" statement. none of these methods worked. ideas appreciated!"

sub gatherdata()  dim wkbkorigin workbook dim originsheet worksheet dim destsheet worksheet  dim resultrow long dim fname string dim rngdest range dim ws worksheet  set destsheet = thisworkbook.worksheets("project cost tracker") set rngdest = destsheet.cells(rows.count, 1).end(xlup).offset(1,0).entirerow fname = dir(thisworkbook.path & "/*.xlsx")  application.screenupdating = false application.displayalerts = false  while fname <> "" , fname <> thisworkbook.name  set wkbkorigin = workbooks.open(thisworkbook.path & "/" & fname)          each ws in wkbkorigin.worksheets                      if ws.name = "distro sheet"                          rngdest.cells(6, 1).value = ws.range("c8").value                         rngdest.cells(6, 5).value = ws.range("h8").value                         rngdest.cells(5, 2).value = ws.range("c10").value                         rngdest.cells(7, 1).value = ws.range("c15").value                         rngdest.cells(8, 1).value = ws.range("c16").value                         rngdest.cells(9, 1).value = ws.range("c17").value                         rngdest.cells(10, 1).value = ws.range("c18").value                         rngdest.cells(11, 1).value = ws.range("c19").value                         rngdest.cells(7, 5).value = ws.range("d20").value                         rngdest.cells(8, 5).value = ws.range("d21").value                         rngdest.cells(9, 5).value = ws.range("d22").value                         rngdest.cells(10, 5).value = ws.range("d23").value                         rngdest.cells(11, 5).value = ws.range("d24").value                      end if                      if ws.name = "execution estimate"                          rngdest.cells(8, 10).value = ws.range("j99").value                         rngdest.cells(9, 10).value = ws.range("j157").value                         rngdest.cells(10, 10).value = ws.range("j186").value                      end if           set rngdest = rngdest.offset(1, 0)         next ws   wkbkorigin.close savechanges:=false  fname = dir()  loop  application.screenupdating = true application.displayalerts = true  end sub 

here corrected code.. , lesson learned use debugger , track significant variables.

    sub gatherdata()           dim wkbkorigin workbook          dim originsheet worksheet          dim destsheet worksheet           dim resultrow long          dim fname string          dim rngdest range          dim ws worksheet           set destsheet = thisworkbook.worksheets("project cost tracker")          set rngdest = destsheet.cells(rows.count, 1).end(xlup).offset(1,0).entirerow          fname = dir(thisworkbook.path & "/*.xlsx")           application.screenupdating = false          application.displayalerts = false           while fname <> "" , fname <> thisworkbook.name          set wkbkorigin = workbooks.open(thisworkbook.path & "/" & fname)           each ws in wkbkorigin.worksheets                      if ws.name = "distro sheet"                          rngdest.cells(6, 1).value = ws.range("c8").value                         rngdest.cells(6, 5).value = ws.range("h8").value                         rngdest.cells(5, 2).value = ws.range("c10").value                         rngdest.cells(7, 1).value = ws.range("c15").value                         rngdest.cells(8, 1).value = ws.range("c16").value                         rngdest.cells(9, 1).value = ws.range("c17").value                         rngdest.cells(10, 1).value = ws.range("c18").value                         rngdest.cells(11, 1).value = ws.range("c19").value                         rngdest.cells(7, 5).value = ws.range("d20").value                         rngdest.cells(8, 5).value = ws.range("d21").value                         rngdest.cells(9, 5).value = ws.range("d22").value                         rngdest.cells(10, 5).value = ws.range("d23").value                         rngdest.cells(11, 5).value = ws.range("d24").value                      end if                      if ws.name = "execution estimate "                          rngdest.cells(8, 10).value = ws.range("j99").value                         rngdest.cells(9, 10).value = ws.range("j157").value                         rngdest.cells(10, 10).value = ws.range("j186").value                      end if           set rngdest = rngdest.offset(1, 0)         next ws      wkbkorigin.close savechanges:=false    fname = dir()     loop     application.screenupdating = true    application.displayalerts = true     end sub