Automate Mexico Customs (Pedimentos) Costing & Tax Calculation in Excel. -- 2

Every day we receive Customs Pedimento PDFs and supplier invoices that share a fixed layout. Someone on my team still re-keys every tariff code, quantity, value and freight line into three different workbooks so our landed-cost formulas can run; a process that eats hours and invites mistakes. I want to replace all that manual effort with a set of solid, reusable Excel macros.

The job revolves around two main functions: first, automatic extraction of the required fields from each document and precise placement in the correct sheets; second, strict validation that every number copied balances against document totals, with clear prompts whenever something looks wrong. Because the formats are consistent, the macro can rely on defined coordinates or named ranges, but it must be flexible enough to handle the occasional missing line or extra charge without breaking.

Deliverables

• An Excel workbook or add-in containing clean, well-commented VBA (or Power Query if you feel it simplifies extraction) that:
– Pulls data from the formatted Customs and Invoice files
– Drops each value into the designated cells across our cost and tax workbooks
– Runs validation/error-checking routines and highlights discrepancies
– Creates a simple log of each processed shipment for audit purposes

• Quick user guide or a brief hand-off session so my team can maintain or extend the scripts.

Everything should remain a one-click experience inside Excel. If you have completed similar VBA or automation projects, describe your approach and timeline so we can move forward quickly.