Create Vendor Purchase Orders from Excel Master Data with VBA
Question details
Create a macro-driven process that transfers purchase-order items from a master worksheet into vendor-specific purchase-order templates.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The user needs to organize purchase-order requests by vendor and export each vendor's items with consistent item, quantity, unit price, and total fields.
- Observed behavior
- The workbook structure has inconsistent headers, missing or unclear purchase-order fields, and button macros that may be linked to another workbook.
Use one consistent header row in the master data and every export template. Confirm that each request row has a vendor, item description, quantity, unit price, and a calculated order total before creating or running the export macro.
Standardize the master sheet and vendor purchase-order template
Use this first to remove the structural issues that prevent reliable VBA filtering and copying.
A vendor export process works best when source and destination fields use the same names and each source row represents one purchase-order line. Do not use a formatting macro to rename headers after data entry, because VBA and Advanced Filter depend on stable field names.
On the master purchase-order request sheet, place headers in one row with clear names such as Vendor, Item, Quantity, Unit Price, and Order Total. Convert the range to an Excel Table by selecting the data and choosing Insert > Table.
In the Order Total column, enter a formula such as =[@Quantity]*[@[Unit Price]] if the master data is an Excel Table. Fill the formula down and verify that quantities and prices are numeric values.
On each vendor purchase-order template, create a line-item area with matching column names: Vendor, Item, Quantity, Unit Price, and Order Total. Keep the purchase-order number, vendor address, and approval fields outside the line-item header row.
Right-click each Export or Paste Values button and select Assign Macro. Confirm that the assigned macro name exists in the current workbook under Developer > Macros; reassign or remove buttons linked to another workbook.

Export each vendor's rows with a VBA filter-and-copy macro
Use this after the headers are consistent and the destination template has a defined line-item range.
Use Advanced Filter when a VBA macro is not required
Use this option for a simpler vendor-by-vendor export process without maintaining VBA code.
Organize Vendor Purchase Orders with WPS Spreadsheets
WPS Spreadsheets can help you standardize a master purchase-order table, filter requests by vendor, copy matching line items into a reusable purchase-order template, and review totals before sending an order. Its familiar spreadsheet interface makes it easy to migrate an existing purchase-order workflow.
- 1. Create a master purchase-order table: Open the request workbook in WPS Spreadsheets, select the purchase-order data, and use the table or formatted range tools to keep Vendor, Item, Quantity, Unit Price, and Order Total in consistent columns.
- 2. Calculate line totals: In the Order Total column, enter a multiplication formula using the Quantity and Unit Price cells, then fill it down for all purchase-order requests.
- 3. Filter one vendor: Select the master header row and choose Data > Filter. Open the Vendor filter arrow and select the vendor whose purchase-order lines you want to export.
- 4. Copy filtered lines to the template: Select the visible filtered rows, copy them, open the vendor purchase-order template, and use Paste Special > Values in the line-item section.
- 5. Review and log the order: Compare the template total with the filtered source total, then add the approved order amount and vendor name to a Yearly Totals worksheet.

Frequently Asked Questions
What columns should a vendor purchase-order master sheet include?
At minimum, include Vendor, Item or Item Description, Quantity, Unit Price, and Order Total. Add useful control fields such as Request Date, PO Number, Status, and Exported Date when you need approval tracking.
Why does my VBA vendor export macro fail to copy the right rows?
The usual cause is inconsistent headers, blank vendor names, or a macro that references the wrong workbook, worksheet, or column index. Check the actual header names and use the current workbook's table and worksheet names in the code.
Can I use Advanced Filter instead of VBA for vendor purchase orders?
Yes. Use Data > Advanced Filter to copy records for one vendor to another location. The source headers and destination output headers must match exactly for the fields you want to export.
Should exported purchase-order rows be deleted from the master sheet?
Do not delete them until the exported purchase order has been checked and approved. A safer approach is to add an Exported or Status column, then archive completed rows after verification.
How can I track yearly totals by vendor?
Store each approved order in a Yearly Totals table with Vendor, Order Date, PO Number, and Order Total. Create a PivotTable or use SUMIFS to calculate totals for each vendor and year.




