excel - How to merge or copy a column from specific worksheets in multiple workbooks to a new workbook? -

i copy column worksheet x of workbook 1 (up workbook 100) , column worksheet y of same workbooks new workbook or open , empty workbook. column sheet x should copied column of specific sheet xmerge , column sheet y should go column of specific sheet ymerge in new workbook. additional copied columns should inserted columns left right, respectively next column , b, c etc.

since there lot of workbooks have consider automate process vba macro.

details: workbooks in 1 folder. amount of filled cells in different columns not equal , cells can contain no or error data. use excel 2010.

i tried modify following macro code suit needs, unfortunately without success. (source: https://msdn.microsoft.com/en-us/library/office/gg549168(v=office.14).aspx)

this macro copies rows rows instead of columns columns. therefore suppose changes in macro solve problem. irrespective of macro grateful every help.

sub mergeallworkbooks() dim summarysheet worksheet dim folderpath string dim nrow long dim filename string dim workbk workbook dim sourcerange range dim destrange range  ' create new workbook , set variable first sheet.  set summarysheet = workbooks.add(xlwbatworksheet).worksheets(1)  ' modify folder path point files want use. folderpath = "c:\users\peter\invoices\"  ' nrow keeps track of insert new rows in destination workbook. nrow = 1  ' call dir first time, pointing excel files in folder path. filename = dir(folderpath & "*.xl*")  ' loop until dir returns empty string. while filename <> ""     ' open workbook in folder     set workbk = workbooks.open(folderpath & filename)      ' set cell in column file name.     summarysheet.range("a" & nrow).value = filename      ' set source range a9 through c9.     ' modify range workbooks.      ' can span multiple rows.     set sourcerange = workbk.worksheets(1).range("a9:c9")      ' set destination range start @ column b ,      ' same size source range.     set destrange = summarysheet.range("b" & nrow)     set destrange = destrange.resize(sourcerange.rows.count, _        sourcerange.columns.count)      ' copy on values source destination.     destrange.value = sourcerange.value      ' increase nrow know copy data next.     nrow = nrow + destrange.rows.count      ' close source workbook without saving changes.     workbk.close savechanges:=false      ' use dir next file name.     filename = dir() loop  ' call autofit on destination sheet  ' data readable. summarysheet.columns.autofit end sub 

this code right, said before, copies rows rows instead of columns columns. how can modify deal columns?

sample code review

to modify code have found online, must first understand doing.

in short, iterating on each workbook , performing following tasks within loop:

1. get data want
2. print in our summary sheet, starting nrow
3. increase nrow last row of our current data (so next group starts in correct place)

what have do?

in our application, want make nrow refer column, such next data prints right instead of below previous data. then, when increment nrow, need count number of columns in our destination instead of number of rows. changes, therefore must occur following lines, nrow occurs:

summarysheet.range("a" & nrow).value = filename 

set destrange = summarysheet.range("b" & nrow) 

nrow = nrow + destrange.rows.count 

our first order of business should rename nrow ncol or more fitting, since no longer counting rows.

next, let's make our new variable ncol refer column start instead of row. recommend using cells function aid in this. cells allows refer cell using index numbers, cells(1,2) instead of string range("a2").

summarysheet.cells(1, ncol) = filename ' prints filename in first row 

set destrange = summarysheet.cells(2, ncol) ' data starts in second row 

now left increment ncol based on number of columns find instead of number of rows.

ncol = ncol + destrange.columns.count 

by holding row static , passing ncol column, incrementing ncol based on number of columns, our data print sequentially right instead of downwards.

as sample code you've pasted says, have determine size of range taking sourcesheet match needs. code above blindly takes a9:c9 need change reference appropriate range in source (like a1:a12 if want first 12 rows in column a).

note: code snippets have written here demonstrative purposes only. have not been tested , not intended copied , pasted application.