Data to Invoice Excel Macro

Question

I've doing these manually for the last for the last year and hoping I can get some help and guidance on how to execute this better using Excel

Sheet 1

Client Name | SKU  | Description | Oct Qyt | Oct fee | Nov Qty | Nov Fee | Dec Qty | Dec Fee | Total
John, Doe   | 1234 | red wine    |      12 |     $12 |      12 |     $12 |      12 |     $12 | $36.00
John, Doe   | 3456 | white wine  |       6 |      $6 |       3 |      $3 |       0 |      $0 |  $9.00
Kent, Clark | 6789 | etc.. 

Same client has multiple SKUs and SKUs vary in quantity each month and I need to figure out the total.

I need to translate the above data to one invoice by client name:

SKU, Description, Oct Units, Fee, Nov, Units, Fee, TOTAL.

Is there a way to easily process this in macros or with vlookups? I tried vlookups and, since there are multiple products under one person, it's not working properly.

I need to do this to generate over 150 invoices, save them individually, convert to pdf, and email it to clients.

Answer 1

Absolute easiest way would be with a pivot table.

If you want to do something using macros, you could start by creating a sheet for each client. I use this macro sometimes by highlighting a distinct list of names and running the macro:

Sub CreateSheetsWithTheseNames()
    Dim CurName As Range

    For Each CurName In Selection
        ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = CurName.Value
    Next CurName
End Sub

This could be modified to just look through column A and creating sheets for every name that does not already have a sheet with that name.

If you had a pivot table setup already, the above macro could be modified to copy the pivot table sheet for each client and filtered accordingly. From there, each sheet can be saved as a PDF.

I could probably expound some on the macros if need be.

Answer 2

For the sake of an answer.

Try a PivotTable, with Client Name in Report Filter, SKU and Description in Row Labels and Sum of each of Oct Qyt, Oct fee, Nov Qty, Nov Fee for Σ Values.