I have tried solutions in other posts like this and so far nothing has worked :/
I have >200 separate excel files, and essentially I would like a specific sheet from each of these files combined into a single sheet in a separate file.
- Each of the original files has multiple sheets - the sheet I'm after is named FIELD (or Field or field, not sure if the capitalisation matters). - It's not always in the same position in each file, e.g. it may be the first or second sheet.
- Overall the headers in each of the FIELD sheets are the same, though there may be some variations and likely position is not the same (i.e. a header in column A in one sheet may be in column C in another).
Is there a way to grab specifically the FIELD sheet from each file and combine them into a single sheet in a separate file where if the headers match they all fill into that column, and if there is a new header it just adds another column to the end?
So for example:
Adding one sheet with A, B, D, C and five entries to another sheet with A, B, C, Da and five entries to produce a master sheet with A, B, C, D, Da - 10 entries under A, B, C and five entries each under D and Da
I am happy to clarify if any of that is confusing! I know this is probably quite a picky and specific question but any help would be greatly appreciated!
Update: I unfortunately have no skills in VBA other than small edits in existing code - have to admit I was hoping there may be an easier way. But we have an Excel expert here (who is very time poor, and I was hoping I could come to him with more than this vague blurb I've put together here :/). So some of the other posts where I tried to tailor their code: How to Merge Data From Multiple Excel Files into a Single Excel File or Access Database? Create New Spreadsheet of data from many Excel workbooks https://stackoverflow.com/questions/26455076/combine-multiple-excel-workbooks-into-a-single-workbook https://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html
