WPS Spreadsheet VBA Macro to Transpose Product Sizes and Quantities
Question details
The user needs to transform product records so that each size and its quantity becomes a separate row on a new worksheet.
- Product
- WPS Spreadsheet VBA
- Device & OS
- not provided
- Scenario
- Product details are stored in columns A:F, while size headings and quantities occupy columns O:U. The user wants to normalize this layout for receiving or reporting.
- Observed behavior
- The source data remains in a wide format, with multiple size columns across each product row instead of one size and quantity pair per output row.
Confirm that the source worksheet is named "BL", the product headers are in row 7, and the product data begins in row 8. Verify that size headings are in O7:U7 and the corresponding quantities are in O:U for each product row.
Use VBA to create one output row for every product size and quantity
Use this solution when the workbook follows the stated BL sheet layout and you want to automate the transformation with a macro.
The macro copies columns A:F for each product, reads the size labels from O7:U7, and writes one output row for every nonblank quantity. The resulting Receiving BL sheet contains the product details, Size, and Qty columns.
In WPS Spreadsheet, open the workbook, press Alt+F11, choose Insert > Module, and paste the macro below into the new module.
Run DuplicateProductsForSizes from the VBA editor or use Developer > Macros. The macro recreates a sheet named Receiving BL and writes the normalized data there.
Open the Receiving BL worksheet and review columns A:F for the product details, column G for Size, and column H for Qty.
Sub DuplicateProductsForSizes() Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim lastRow As Long Dim sourceRow As Long Dim sizeColumn As Long Dim outputRow As Long Dim quantityValue As Variant Set sourceSheet = ThisWorkbook.Worksheets("BL") Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Receiving BL").Delete On Error GoTo 0 Application.DisplayAlerts = True Set targetSheet = ThisWorkbook.Worksheets.Add(After:=sourceSheet) targetSheet.Name = "Receiving BL" sourceSheet.Range("A7:F7").Copy Destination:=targetSheet.Range("A1") targetSheet.Range("G1").Value = "Size" targetSheet.Range("H1").Value = "Qty" lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row outputRow = 2 For sourceRow = 8 To lastRow For sizeColumn = 15 To 21 quantityValue = sourceSheet.Cells(sourceRow, sizeColumn).Value If Len(Trim$(CStr(quantityValue))) > 0 Then targetSheet.Cells(outputRow, 1).Resize(1, 6).Value = _ sourceSheet.Cells(sourceRow, 1).Resize(1, 6).Value targetSheet.Cells(outputRow, 7).Value = _ sourceSheet.Cells(7, sizeColumn).Value targetSheet.Cells(outputRow, 8).Value = quantityValue outputRow = outputRow + 1 End If Next sizeColumn Next sourceRow targetSheet.Columns("A:H").AutoFit End Sub
Convert the size columns with an unpivot workflow
Use an unpivot-style data transformation when you prefer a table-based approach and do not need a VBA macro.
Normalize Product Size and Quantity Data in WPS Spreadsheet
WPS Spreadsheet lets you organize wide product tables, run supported VBA macros, and produce a clean row-based result for receiving or reporting. You can then save the transformed workbook in a familiar Office format.
- 1. Open the workbook: Launch WPS Office and open the workbook containing the BL worksheet.
- 2. Insert the macro: Press Alt+F11, select Insert > Module, and paste the size-and-quantity transformation macro.
- 3. Run the transformation: Run DuplicateProductsForSizes from the macro list to create the Receiving BL worksheet.
- 4. Review and save: Check the generated Size and Qty columns, then save the workbook as XLSX or XLSM as needed.
Frequently Asked Questions
Which columns does the VBA macro use for product details and sizes?
The macro copies product details from columns A:F, reads size headings from O7:U7, and reads the corresponding quantity values from columns O:U.
Where does the macro place the transformed data?
It creates or recreates a worksheet named Receiving BL. Product details are placed in columns A:F, Size is placed in column G, and Qty is placed in column H.
How can I change the source worksheet name?
Edit Set sourceSheet = ThisWorkbook.Worksheets("BL") and replace BL with the exact name of your source worksheet. The sheet name must match, including spaces and punctuation.
Will zero quantities be included in the output?
Yes. A numeric zero is not blank, so it is included. Empty cells are skipped. If zero quantities should be excluded, add a numeric condition such as If IsNumeric(quantityValue) And quantityValue <> 0 Then.
What should I change if the size columns are not O:U?
Update the loop boundaries. The code uses columns 15 through 21 for O:U; replace those numbers with the column numbers for your actual size range and update the header location if necessary.
