vba - Combine multiple Excel workbooks into a single workbook -

vba - Combine multiple Excel workbooks into a single workbook -

i novice @ visual basic. can utilize either excel 2010 or excel 2013 task.

i have dozens of workbooks info on first worksheet of each. illustration one.xlsx, two.xlsx, three.xlsx, four.xlsx each contain info on respective sheet1.

i need info on sheet1 each workbook combined single workbook sheets named file name of original workbook. illustration combined.xlsx have 4 sheets named one, two, three, four. in every case info on underlying worksheets should copied , combined in new workbook shown below.

the format need

i found macro / add-in online gets me close need using open files add together in choice.

http://www.excelbee.com/merge-excel-sheets-2010-2007-2013#close

the open files add-in allows me aggregate various workbook's worksheets single workbook. tabs not named name of original file.

correct aggregation of sheets, wrong worksheet names.

for underlying workbooks in same folder. ability browse , select files nice if ever changes if difficult, indicating directory path in visual basic code work. far resultant combined output ought new workbook, filename of new workbook isn't important. called combined.xlsx example.

the next accomplishes task.

option explicit private sub commandbutton1_click() dim directory string, filename string, sheet worksheet, total integer dim wrdarray() string application.screenupdating = false application.displayalerts = false directory = "c:\test\" filename = dir(directory & "*.xl??") while filename <> "" workbooks.open (directory & filename) wrdarray() = split(filename, ".") each sheet in workbooks(filename).worksheets workbooks(filename).activesheet.name = wrdarray(0) total = workbooks("import-sheets.xlsm").worksheets.count workbooks(filename).worksheets(sheet.name).copy after:=workbooks("import-sheets.xlsm").worksheets(total) goto exitfor: next sheet exitfor: workbooks(filename).close filename = dir() loop application.screenupdating = true application.displayalerts = true end sub

excel vba excel-vba