Append data from multiple workbooks with sometimes different headers (1 Viewer)

[Lanser]

Hi I,
I have a results spreadsheet emailed to me every week 2k rows and I want to be able to add each weeks data to a master sheet/book for analysis.
I have searched a lot and found some code to do what I want as long as each sheet has the same format/columns, however occasionally there may be an extra column or one missing or they may be in different order, although all headers will be the same format/spelling across sheets, but also I wont know if a new column is to be added until weekly sheet arrives.

eg.
All sheets will have A (unique value) All will have ABC some ABCD some ABCF some ABCEF some ABCDE and I would like to have a master sheet with ABCDEF.

Thanks in advance

 

[Frosty328]

I think I may of found a soultion to your problem.
I decided to create an example myself to help guide you. Hopefully you can fill in the gaps of your own spreadsheet from my example.
Example:

Worksheet names: Master, Sales and Summary.
Relevant columns: Master- ABCDEF. Sales- ABCD. Summary- AEF.
Inital data locations: Master- A2:F10. Sales- A2:10. Summary- A2:C10.
Relevant data for Master: Sales- A2:10. Summary- B2:C2.

1. In the master worksheet, in cell A2 type in =Sales!A2:10.
2. I then dragged A2 across in the master worksheet, A2:10.
3. In the master worksheet, in cell E2 type in =Summary!B2:C2. This is because the unique field (A) is already in the master worksheet from the sales worksheet.
4. Do the same again. Drag E2 across, E2:F10.
5. Now I have all the relevant data with the relevant columns from the 2 other sheets, the master worksheet is complete.

The important thing to remember is using the formula =Worksheet name!Cell range and to drag them across where needed. If this does work for you, make sure to delete the code that you used.

A bit worried as you would have to delete the data in those worksheets every week and input the new data, so I'm not sure if that is what you wanted.

I hope this was helpful

 

[Lanser]

Hi Thanks for the try but not quite what I'm looking for, I have attached a couple of demo workbooks each month needs to add to the Master then I can extract, trend and graph individual categories