A set of macros written in VBA (Visual Basic for Applications) that automated inventory tasks inside Excel: consolidating data from several sources, building pivot tables, generating reports and applying filters. Each macro covers one step of the inventory-and-sales workflow, and together they replaced hours of manual work that was prone to copy-paste errors.

1. Bodega() — warehouse consolidation

Module: Bodega_de_datos. Processes a "TCEDI" file chosen by the user, summarizes it with a pivot table and transfers the result to the preparation template, enriching it with lookups in other workbooks.

It opens the file, cleans the columns, asks the user to pick the TCEDI, adds a concatenation column to identify each element and builds a pivot table with PivotCaches. The result is copied to Plantilla preparación.xlsm, where it performs replacements (e.g. "B.Eco" → "B. eco"), splits text with Text to Columns and closes the helper files. The template ends up with id_cia, concatenado and the other fields already calculated.

Excel sheet processed by the Bodega macro
Output of Bodega(): the preparation template with the computed fields.

2. Consolidar_BDT() — dispatch consolidation

Module: Módulo1. Unifies dispatch information from a "T.Cedi" file, builds a pivot table and completes the data with an item master, then appends the result to a central consolidation workbook (BDT.CEDI.xlsx).

The file is opened with Workbooks.OpenText using delimiters; a concatenation column and a "Sum of Value" pivot table are created; year and month columns are inserted; and XLOOKUP / VLOOKUP pull attributes from Maestro de ítems.txt before everything is pasted into the consolidated workbook.

Consolidated dispatch data
The consolidated dispatch workbook.

3. Disponible() — availability

Module: Disponible. Centralizes item availability across warehouses (S, M, L…), computes total inventory and decides how many units can be shipped — capped at 250 by business rule — then formats and sorts the sheet for the team.

It clears columns A:Z, writes the headers (photo, item, rating, total stock…), pulls data from sheet "B034" through filters, inserts the sum and VLOOKUP formulas, flags new and blacklisted items, applies the 250-unit cap, breaks external links and orders the columns for the final report.

4. Listado() — final listing

Module: Listado. Generates a new workbook (e.g. LIST hombre.xlsx) with the final, formatted information: photo, item, gender, category, total inventory and sizes.

It creates the workbook, copies data from the import sheet into "SALE", adds the inventory totals, applies colors, fonts and borders, sorts by category and gender and opens a SaveAs dialog for the destination. A validation pivot table is generated, temporary sheets are removed and only the final sheet remains.

Final formatted listing generated by the Listado macro
The formatted listing produced by Listado().

5. VxP() and VxP_EC() — filters and color scales

Module: VxP. Apply filters and conditional formatting to every sheet in a workbook to highlight rows where key columns are above 3 or below 0.2. Each sheet is visited in turn; AutoFilter keeps only the rows that match, and a three-color ColorScale is applied to columns N:P (or N:S). The only difference between the two macros is the range they cover: A:P versus A:S.

Takeaways

Together the macros automate cleaning, consolidation and reporting quickly and reliably, saving time and removing manual errors. Each one plays a specific role — warehouse, consolidation, availability, listing and filters — and they chain into a complete inventory-and-sales workflow.