Excel VBA import of various excel files to master sheet -

Excel VBA import of various excel files to master sheet -

i have master excel workbook , want create macro import info specified range 7 excel files. these files same in construction except actual data. import macro/button want open files dialogue, select files , allow macro add together info in range master 1 one. have taken inspiration post here, makes work 1 single file: adjusted code have able select 7 files , dynamically add together target range 1 one.

sub getdata() dim slavebook workbook dim filter string dim caption string dim slavefilename string dim slaveworkbook workbook dim targetworkbook workbook set targetworkbook = application.activeworkbook filter = "team file (*.xlsm),*.xlsm" caption = "please select team file" slavefilename = application.getopenfilename(filter, , caption) set slaveworkbook = application.workbooks.open(slavefilename) dim targetsheet worksheet set targetsheet = targetworkbook.worksheets("master") dim sourcesheet worksheet set sourcesheet = slaveworkbook.worksheets("interface") targetsheet.range("b5", "j8").value = sourcesheet.range("b5", "j8").value slaveworkbook.close end sub

sub getdata() dim slavebook workbook dim filter string dim caption string dim slavefilename string dim slaveworkbook workbook dim targetworkbook workbook = 1 = 1 7 set targetworkbook = application.activeworkbook on error goto errorhandler filter = "team file (*.xlsm),*.xlsm" caption = "please select team file" slavefilename = application.getopenfilename(filter, , caption) set slaveworkbook = application.workbooks.open(slavefilename) on error goto 0 on error goto err2 dim targetsheet worksheet set targetsheet = targetworkbook.worksheets("master") dim sourcesheet worksheet set sourcesheet = slaveworkbook.worksheets("interface") if = 1 targetsheet.range("b5", "j8").value = sourcesheet.range("b5", "j8").value if = 2 targetsheet.range("b9", "j12").value = sourcesheet.range("b5", "j8").value if = 3 targetsheet.range("b13", "j16").value = sourcesheet.range("b5", "j8").value if = 4 targetsheet.range("b17", "j20").value = sourcesheet.range("b5", "j8").value if = 5 targetsheet.range("b21", "j24").value = sourcesheet.range("b5", "j8").value if = 6 targetsheet.range("b25", "j28").value = sourcesheet.range("b5", "j8").value if = 7 targetsheet.range("b29", "j32").value = sourcesheet.range("b5", "j8").value slaveworkbook.close false 'wont prompt save changes (will close without saving), 'remove false if need save changes = + 1 next exit sub errorhandler: msgbox "you didn't select valid file!" exit sub err2: msgbox "error - reason required sheet not found in slave workbook" exit sub end sub

updated - have changed code should save info "master" sheet underneath eachother. simple way of doing it, , restricts opening 7 files before code ends. if wanted add together more in future extend array , range code or modify range code lastly available row paste info on (lastrow = range("j65536").end(xlup).row) place start

excel excel-vba import