So, ideally, the consolidated workbook should have column headers and then entire worksheet data copied into it. to select all of them press control A and it will select all files and then once clicked ok then a new workbook will be opened with all of the merged data in single sheet. it will also have an helper column identifying up to what row belongs to which file. Please, I will like to save the consolidated workbook it in a specific folder with a specific name. Sorry, I'm new to VBA and I will really appreciate your help and effort. The Consolidation Assistant add-in for Microsoft Excel is designed to help you consolidate data from multiple workbooks or from multiple worksheets.
She could then use pivot tables and data filtering to analyze the data The Consolidation Assistant has several features that allow you to conveniently view your data. Another sets the same cell on all selected sheets to the upper left corner.And at the same time sets the zoom to the same setting on all the worksheets.Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.i have some questions, as i may have not understood your question properly.One of the steps it allows you to take is to transpose data very easily.
Transposing a data table is basically rotating your data from rows to columns, or from columns to rows. Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.
One of our customers recently received 47 workbooks and needed to get data from each sheet named "data" in these files.
With the Consolidation Assistant, she was able to first create a new workbook that pulled the data sheets (and renamed them so she knew where they came from) into one workbook. She then used the consolidate sheets feature to consoldate all 47 sheets into one worksheet.
Open(every Obj) 'change "A2" with cell reference of start point for every files here 'for example "B3: IV" to merge all files start from columns B and rows 3 'If you're files using more than IV column, change it to the latest column 'Also change "A" column on "A65536" to the same column as start point Range("A2: IV" & Range("A65536"). "The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years.
I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing.
But the Consolidation Assistant came to his rescue!