Automatically create invoices from sales invoices3 Excel (Excel)

Previous.Automatically create invoices from sales invoices 2 Excel (Excel)We will continue with the invoicing process, pulling from the If you haven't read it yet.Automatically create invoices from sales invoices1 Excel (Excel)Please also read

Putting details on invoices (using macros)

Name the billing name cell.

In the example Excel, the billing name is entered in the "B6" cell. When switching billing partners, the value of this cell will be changed, so this cell should always be referenced.

Click on the "B6" cell, select the upper left cell number where it appears, and type "billing address."

Then press the "Enter" key to confirm. The "B6" cell is now renamed "Billing address".

Describing macros

Sub Reflecting the statement()
    Dim meisaish As Worksheet: Set meisaish = Sheets("Sales invoice")
    Dim thissh As Worksheet: Set thissh = Sheets("Invoice")
    Dim i1 As Long: i1 = 4 'Line number of the sales detail sheet
    Dim i2 As Long: i2 = 16 'Invoice sheet detail line number
    Dim n As Long 'Number of columns in sales detail sheet
    Dim LastRow As Long: LastRow = 40
    Dim seikyusaki As String: seikyusaki = thissh.Range("Billing address").Value

    ClearContents thissh.Range(Cells(i2, 2), Cells(LastRow, 5)).ClearContents 'Clear input value
    thissh.Range(Cells(i2, 2), Cells(LastRow, 5)).Interior.ColorIndex = 0 Clear the 'fill'.
    
    Do Until meisaish.Cells(i1, 2) = ""
        If meisaish.Cells(i1, 3) = seikyusaki Then
            For n = 4 To 7
                Cells(i2, n - 2).Value = meisaish.Cells(i1, n).Value
                If i2 Mod 2 = 0 Then ' If the number of rows is even, do the following
　　　　　　　　　　　Fill 'rows' with gray
                    thissh.Cells(i2, n - 2).Interior.Color = RGB(217, 217, 217)
                End If
            Next
            Color = RGB(217, 217, 217) End If Next
        End If
        i1 = i1 + 1
    Loop

End Sub

If you run this

Change the billing address to "BBB" and run it again.

The "Sales Details" sheet will be replaced with the newest one. If the contents of the "Sales Details" sheet have changed, you can run it again to replace it with the most recent version.

Stripe all blank rows as well.

Add the following code just before "EndSub

Sub Reflecting the statement()
    Dim meisaish As Worksheet: Set meisaish = Sheets("Sales invoice")
    Dim thissh As Worksheet: Set thissh = Sheets("Invoice")
    Dim i1 As Long: i1 = 4 'Line number of the sales invoice sheet
    Dim i2 As Long: i2 = 16 'Invoice sheet detail line number
    Dim n As Long 'Number of columns on the sales invoice sheet
    Dim LastRow As Long: LastRow = 40
    Dim seikyusaki As String: seikyusaki = thissh.Range("Invoices").Value

    ClearContents thissh.Range(Cells(i2, 2), Cells(LastRow, 5)). 'Clear input value
    thissh.Range(Cells(i2, 2), Cells(LastRow, 5)).Interior.ColorIndex = 0 Clear the 'fill'.
    
    Do Until meisaish.Cells(i1, 2) = ""
        If meisaish.Cells(i1, 3) = seikyusaki Then
            For n = 4 To 7
                Cells(i2, n - 2).Value = meisaish.Cells(i1, n).Value
                If i2 Mod 2 = 0 Then ' If the number of rows is even, do the following
                    Fill 'rows' with gray
                    thissh.Cells(i2, n - 2).Interior.Color = RGB(217, 217, 217)
                End If
            Next
            Color = RGB(217, 217, 217) End If Next
        End If
        i1 = i1 + 1
    Loop
    
    Do Until i2 = LastRow + 1
        For n = 4 To 7
            If i2 Mod 2 = 0 Then ' If the number of rows is even, do the following
                Color = RGB(217, 217, 217) thissh.Cells(i2, n - 2).Interior. Fill 'rows' with gray
            End If
        Next
        i2 = i2 + 1
    Loop
    
End Sub

When executed in this state

Thus, stripes could be added even to rows that do not contain data.

Advantages of using macros

• Flexibility to handle any case.
• No more collapsing of formulas when adding and deleting lines in the sales invoice
• Simple macros can be handled in any Excel version.

Disadvantages of using macros

• Knowledge of Macro (VBA) required
• Macro may not select the correct range when the invoice format is changed
• Increased difficulty of maintenance
• Macros must be enabled on all terminals used.
• If a macro error occurs, it is likely that others will not be able to fix it.