Excel - automated data collection from multiple workbooks with multiple worksheets with different headers

We are a Swiss (Zurich) building materials trader with a VAT registration in France. Every month we have to report purchases/sales to the FR authorities and work with a partner in France. All transactions are recorded manually in Excel and the completed Excel sheets are sent to our partner for further processing. In order to obtain an overview of all transactions (1 transaction = 1 line), the data should be merged into one large list. This is to be done retrospectively for the historically existing data and in addition an automation is to be achieved that pulls together all future data from newly generated workbooks. Data: -12 Workbooks / year -1 Workbook / month -14 worksheets / workbook of which 1 sheet = summary (pivot, of 11 worksheets), 2 worksheets with data input by our partner (no pivot, no influence on summary) -Historically, 46 workbooks have already been created (since February 2017) -400-500 transactions have been generated. Data, or transactions, are entered into the corresponding worksheet according to the appropriate category. The worksheets are all formatted as tables, but have different headers. The columns have different formatting (date, currency, numeric, text) and in a few columns the data (VAT) is calculated with a formula. Why we cannot solve the problem ourselves / challenges: -Tables in worksheets have different headers (should stay that way). -Automation, linking and storage of new worksheets unclear Attached are screenshots and an example of a worksheet with some sample data. If we award you this contract, we will require you to sign a Non Disclosure Agreement because it is confidential information. If you feel up to this challenge, we look forward to hearing from you. Kind regards, Yanick