Manufacturing: VBA macros to Sheets + multi‑step approvals

Context

Inside sales and engineering both touched quotes. Approvals were informal (email threads). Version mix‑ups happened when someone edited an old xlsx.

Challenge

• Business rules (discount bands, BOM lookups, freight estimates) were embedded in VBA and hidden named ranges—fragile and hard to hand off.
• Leadership wanted cloud collaboration without re‑buying an expensive CPQ product.
• Quotes needed to generate customer‑facing PDFs and retain an audit trail of who approved what.

Approach

1. Reverse‑engineered the VBA into documented steps and data dependencies; simplified where spreadsheets were doing redundant work.
2. Recreated calculations in Sheets with strict input validation and protected ranges; moved “workflow” code to Apps Script menus and triggers.
3. Built a lightweight approval flow: draft → sales manager → ops sign‑off, with email notifications and status columns.
4. Used a Google Docs template for PDF generation (Merge from sheet → PDF in Drive → optional Gmail send).

Solution

A Sheets‑first system with custom dialogs for common actions, server‑side generation for PDFs, and all automation in Apps Script so the client owns the code. Mobile users could review numbers in the Sheet app; heavy edits stayed on desktop by policy.

Outcome

• Sales could work from anywhere; no dependency on a single Windows machine for macro execution.
• Approval status became explicit in the sheet—fewer “which version is final?” mistakes.
• Future changes (new product line, tax rule) were scoped as sheet + script updates rather than opaque macro edits.

Related on this site

• VBA to Apps Script migration →
• Sheets automation basics →