Excel VBA Macro to Copy Vendor Items to a PO Template and Totals
Question details
Create a VBA macro that asks for a vendor, filters vendor records on the master purchase-order request sheet, copies the visible items to a PO template in groups of up to 21, and appends all copied records to a totals sheet.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The user needs to prepare vendor-specific purchase-order items from the 3_Master PO Request sheet while maintaining a running record in 4B_2024 Totals.
- Observed behavior
- Only records visible after filtering for the selected vendor should be copied; filtered-out rows must not be included.
Confirm that row 1 contains headers and that the vendor field is in column A, with the fields to copy in columns A:D. Enable macros before running the workbook, and update the sheet names or column references in the code if they differ from your file.
Use a visible-row VBA macro to copy vendor items in 21-row PO blocks
Use this option when your source data uses columns A:D, the vendor is in column A, and you want only the filtered vendor records copied to both destination sheets.
This macro applies an AutoFilter to 3_Master PO Request, uses SpecialCells(xlCellTypeVisible) to process only matching rows, and writes each item to 4A_PO Template and 4B_2024 Totals. A blank row is inserted between PO Template blocks after every 21 copied items.
In Excel, press Alt+F11, select your workbook in Project Explorer, then choose Insert > Module.
Paste the following code into the module: Option Explicit Sub CopyVendorItemsToPOAndTotals() Const FIRST_DATA_ROW As Long = 2 Const VENDOR_COLUMN As Long = 1 'Column A Const FIRST_COPY_COLUMN As Long = 1 'Column A Const COPY_COLUMN_COUNT As Long = 4 'Columns A:D Const BATCH_SIZE As Long = 21 Dim wsSource As Worksheet Dim wsPO As Worksheet Dim wsTotals As Worksheet Dim visibleRows As Range Dim area As Range Dim sourceRow As Range Dim vendor As Variant Dim lastSourceRow As Long Dim poNextRow As Long Dim totalsNextRow As Long Dim batchCount As Long Dim copiedCount As Long Set wsSource = ThisWorkbook.Worksheets("3_Master PO Request") Set wsPO = ThisWorkbook.Worksheets("4A_PO Template") Set wsTotals = ThisWorkbook.Worksheets("4B_2024 Totals") vendor = Application.InputBox("Enter the vendor name to copy:", "Copy Vendor Items", Type:=2) If vendor = False Or Len(Trim$(CStr(vendor))) = 0 Then Exit Sub lastSourceRow = wsSource.Cells(wsSource.Rows.Count, VENDOR_COLUMN).End(xlUp).Row If lastSourceRow < FIRST_DATA_ROW Then MsgBox "No source data was found.", vbExclamation Exit Sub End If If wsSource.AutoFilterMode Then wsSource.AutoFilterMode = False wsSource.Range(wsSource.Cells(1, FIRST_COPY_COLUMN), _ wsSource.Cells(lastSourceRow, FIRST_COPY_COLUMN + COPY_COLUMN_COUNT - 1)).AutoFilter _ Field:=VENDOR_COLUMN, Criteria1:=Trim$(CStr(vendor)) On Error Resume Next Set visibleRows = wsSource.Range(wsSource.Cells(FIRST_DATA_ROW, FIRST_COPY_COLUMN), _ wsSource.Cells(lastSourceRow, FIRST_COPY_COLUMN + COPY_COLUMN_COUNT - 1)).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If visibleRows Is Nothing Then wsSource.AutoFilterMode = False MsgBox "No items were found for vendor: " & vendor, vbInformation Exit Sub End If poNextRow = Application.Max(FIRST_DATA_ROW, wsPO.Cells(wsPO.Rows.Count, FIRST_COPY_COLUMN).End(xlUp).Row + 1) totalsNextRow = Application.Max(FIRST_DATA_ROW, wsTotals.Cells(wsTotals.Rows.Count, FIRST_COPY_COLUMN).End(xlUp).Row + 1) For Each area In visibleRows.Areas For Each sourceRow In area.Rows If batchCount = BATCH_SIZE Then poNextRow = poNextRow + 1 'Leave a blank row before the next PO block batchCount = 0 End If wsPO.Cells(poNextRow, FIRST_COPY_COLUMN).Resize(1, COPY_COLUMN_COUNT).Value = sourceRow.Value wsTotals.Cells(totalsNextRow, FIRST_COPY_COLUMN).Resize(1, COPY_COLUMN_COUNT).Value = sourceRow.Value poNextRow = poNextRow + 1 totalsNextRow = totalsNextRow + 1 batchCount = batchCount + 1 copiedCount = copiedCount + 1 Next sourceRow Next area wsSource.AutoFilterMode = False MsgBox copiedCount & " item(s) copied for vendor: " & vendor, vbInformation End Sub
Return to Excel, press Alt+F8, select CopyVendorItemsToPOAndTotals, and click Run. Enter the vendor name exactly as it appears in column A.

Adjust the column mapping for a different source layout
Use this option when the vendor field or the data fields to copy are not in columns A:D.
Run Vendor Copy Macros in WPS Spreadsheet
WPS Spreadsheet lets you organize vendor purchase requests, work with filtered records, and use VBA-compatible macro workflows in supported desktop environments. Open the macro-enabled workbook, review the sheet and column mappings, then run the vendor-copy procedure to create PO blocks and update totals.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing 3_Master PO Request, 4A_PO Template, and 4B_2024 Totals.
- 2. Verify the source columns: Check that the vendor and item fields match the column numbers used by the macro before running it.
- 3. Open the macro tools: Use the Developer or Macro tools available in your WPS Spreadsheet desktop installation to open the VBA editor or manage the workbook macro.
- 4. Run the vendor-copy procedure: Select the CopyVendorItemsToPOAndTotals macro, enter the vendor name when prompted, and confirm that the PO Template and Totals sheets receive the copied rows.

Frequently Asked Questions
Why does a basic VBA copy loop include rows that were filtered out?
A normal Range.Copy operation can copy the full range even when some rows are hidden by an AutoFilter. Use Range.SpecialCells(xlCellTypeVisible) after applying the filter so the macro processes visible rows only.
How do I change the maximum number of PO items from 21 to another number?
Change the BATCH_SIZE constant near the top of the macro. For example, set BATCH_SIZE = 15 to create blocks of 15 items on 4A_PO Template.
What happens when the selected vendor has no matching records?
The macro checks whether SpecialCells(xlCellTypeVisible) finds any data rows. If none are found, it removes the filter and displays a message without adding rows to either destination sheet.
How can I keep the headers from being copied to the PO Template and Totals sheets?
Keep FIRST_DATA_ROW set to 2 when headers are on row 1. The filtered range begins at row 2, so the source header row is excluded from the copy process.




