1
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
txsmills
Posted 2016-05-20T07:58:00.963
Reputation: 27
Hi txsmills, to answer your question "Is there a way?" the answer is yes. What have you tried so far? Without anything to work with there is little help to be offered. Can you add your current attempt to the question. Thanks. – Tim Wilkinson – 2016-05-20T09:03:13.123
Welcome to Super User! Please note that [SU] is not a script writing service. If you tell us what you have tried so far (including any scripts you are using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
– DavidPostill – 2016-05-20T11:19:28.740Yes, first you would need to write code that going through a directory and opens the books, copies the sheet and closes the books. Then it is all in one document and you can write code that goes sheet to sheet looking for specific headers and combining them into a new sheet. – Raystafarian – 2016-05-20T11:59:15.240
you say "I have tried solutions in other posts like this". Please link to some of them. – user 99572 is fine – 2016-05-21T18:15:17.820