The Challenge: A Very Long Manual Task

After we have the replenishment report, the next step is to create a "store pullout" report. This is a long list of items we need to pull from our physical stores to restock the online store.

My colleague was doing this in Excel, but it was a very manual process. For thousands of rows of items, she had to figure out which stores to pull from. The rule is to get from the stores with the highest inventory first, and to try to distribute the pullout so we don't empty out just one store. She told me that during peak season, this task would sometimes take her half a day or even a whole day.

Her method was to highlight cells in different colors to mark how many items to pull, and then later replace the values. I saw how repetitive it was and thought, "This can definitely be automated."

My Solution: A One-Click VBA Script

To solve this, I decided to create an Excel VBA script. I vibe-coded it with AI, guiding it to build a macro that could handle the whole complex logic.

The script I wrote automates everything. It sorts all the stores by inventory for each item and then automatically assigns the pullout quantities, starting with the highest-stocked stores and distributing the pullout evenly. The final result is a perfect, error-free report that is generated in just a few minutes. What used to take a full day is now just a single click.

Live Demo in Action

Here is a short video showing the VBA script in action. You can see how it transforms the slow, manual task into an instant, automated report.

Store pullout VBA demo by Abegail Fontanilla

The VBA Script

Here is a part of the VBA code I wrote that handles the data sorting and the main pullout logic.

Sub StorePullout()
    Dim wsInput As Worksheet, wsOutput As Worksheet
    Dim lastRow As Long, lastCol As Long, skuRow As Long, storeCol As Long
    Dim quantityNeeded As Long, pulledQuantity As Long, storeInventory As Variant
    Dim outputRow As Long, i As Long, j As Long
    Dim stores() As Variant, storeCount As Long, temp As Variant
    Dim sheetName As String

    ' Allow user to select the input sheet
    On Error Resume Next
    sheetName = Application.InputBox("Select the input sheet by clicking on any cell in the sheet:", "Select Input Sheet", Type:=8).Parent.Name
    On Error GoTo 0

    ' Check if the user canceled the input box
    If sheetName = "" Then
        MsgBox "No sheet selected. Macro canceled.", vbExclamation
        Exit Sub
    End If
    ' ... rest of the VBA code ...
End Sub

Store Pullout Automation with Excel VBA

The Challenge: A Very Long Manual Task

My Solution: A One-Click VBA Script

Live Demo in Action

The VBA Script

Project Overview