Search code examples
excelvbainvoice

Sort items per customer


I am making a tool in excel VBA to bulk create some kind of invoices to each customer. We are making LIVE streams and selling kids clothing, then we write all our orders to excel sheet. Example: orders list

Then we have to sort all those orders by customer (there are many of them) and create some kind of invoice for each customer. Example: "invoice template"

I use this code to bulk create those and download as pdfs.

Sub Create_PDF_Files()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim dsh As Worksheet
Dim tsh As Worksheet
Dim setting_Sh As Worksheet

Set dsh = ThisWorkbook.Sheets("uzsakymai")
Set tsh = ThisWorkbook.Sheets("lapukas")
Set setting_Sh = ThisWorkbook.Sheets("Settings")


Application.DisplayStatusBar = True
Application.StatusBar = ""

Dim i As Integer
Dim File_Name As String

For i = 2 To dsh.Range("A" & Application.Rows.Count).End(xlUp).Row

Application.StatusBar = i - 1 & "/" & dsh.Range("A" & Application.Rows.Count).End(xlUp).Row - 1

tsh.Range("D1").Value = dsh.Range("C" & i).Value
tsh.Range("A4").Value = dsh.Range("B" & i).Value
tsh.Range("B4").Value = dsh.Range("A" & i).Value & " - " & dsh.Range("E" & i).Value
tsh.Range("P4").Value = dsh.Range("D" & i).Value

File_Name = dsh.Range("A" & i).Value & "(" & dsh.Range("C" & i).Value & "-" & dsh.Range("D" & i).Value & ").pdf"
tsh.ExportAsFixedFormat xlTypePDF, setting_Sh.Range("F4").Value & "\" & File_Name

Next i

Application.StatusBar = ""

MsgBox "Done"
End Sub

But what it does is creating invoice for each item. EXAMPLE

Any ideas how could I make it work for me as I want it to work?

---EDIT---

After ALeXceL answer, it seems to have some bugs. I changed my code to his code, and I see some progress in creating this program, but what it does, is it shows first item correctly, but all the other items are appearing starting on A24 cell. EXAMPLE

---EDIT---

IT WORKS!!!


Solution

  • Assuming that "uzsakymai" is "orders", the 'data sheet' (dsh) and "lapukas" is the 'template' sheet (tsh), I did these changes, added some counters, in order to the logic flows the right way:

    Important: before put this code to run you MUST classify the 'orders' table (dsh, or "uzsakymai") first by Name, then, by Size (as you wish, according to the images posted)

    Option Explicit
    
    Sub Create_PDF_Files()
    
        Dim Orders_sh As Worksheet
        Dim Template_sh As Worksheet
        Dim setting_Sh As Worksheet
        Dim oCell As Excel.Range
        Dim strKey_TheName As String
        Dim lngTemplate_A As Long
        Dim lngSumOfItems As Long
        Dim dblSumOfValues As Double
        
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
    
        Set Orders_sh = ThisWorkbook.Sheets("uzsakymai")
        Set Template_sh = ThisWorkbook.Sheets("lapukas")
        Set setting_Sh = ThisWorkbook.Sheets("Settings")
    
    
        Application.DisplayStatusBar = True
        Application.StatusBar = ""
    
        Dim lngI As Long
        Dim File_Name As String
    
        'At this point, the Orders_sh worksheet should already have its fields properly sorted/ordered. (Name, then Size)
        lngI = 2
        Application.StatusBar = lngI - 1 & "/" & Orders_sh.Range("A11").End(xlUp).Row - 1 'a maximum of 10 items can be written here!
        Set oCell = Orders_sh.Range("A" & lngI) ' the initial cell
    
        Do
            strKey_TheName = UCase(Orders_sh.Range("C" & lngI).Value)
            lngSumOfItems = 0
            dblSumOfValues = 0
    
            Do
                Template_sh.Range("D1").Value = Orders_sh.Range("C" & lngI).Value
                lngTemplate_A = IIf(lngSumOfItems = 0, 4, Template_sh.Range("A10").End(xlUp).Offset(1, 0).Row)
                Template_sh.Range("A" & lngTemplate_A).Value = Orders_sh.Range("B" & lngI).Value
                Template_sh.Range("B" & lngTemplate_A).Value = Orders_sh.Range("A" & lngI).Value & " - " & Orders_sh.Range("E" & lngI).Value
                Template_sh.Range("P" & lngTemplate_A).Value = Orders_sh.Range("D" & lngI).Value
                
                lngSumOfItems = lngSumOfItems + 1
                dblSumOfValues = dblSumOfValues + Orders_sh.Range("D" & lngI).Value
                
                File_Name = lngSumOfItems & "(" & Orders_sh.Range("C" & lngI).Value & "-" & VBA.Round(dblSumOfValues, 0) & ").pdf"
                lngI = lngI + 1
                Set oCell = oCell.Offset(1, 0)
            Loop Until strKey_TheName <> UCase(oCell.Offset(0, 2).Value)
    
            Template_sh.ExportAsFixedFormat xlTypePDF, setting_Sh.Range("F4").Value & "\" & File_Name
            Template_sh.Range("D1").Value = ""
            Template_sh.Range("A4:P10").ClearContents
        Loop Until Len(oCell.Value) = 0
    
        Application.StatusBar = ""
        MsgBox "Done"
    End Sub