Template Invoice dan Laporan Penjualan

Tutorial Template Invoice dan Laporan Penjualan ini terdiri dari beberapa Sesion:

• Sesion # 01. Membuat Worksheets Product
• Sesion # 02. Membuat Worksheets AddStock
• Sesion # 03. Membuat Worksheets Invoice
• Sesion # 04. Membuat Worksheets Statement
• Sesion # 05. Membuat Worksheets Template

 Sesion #4. Membuat Worksheets Statement

Dynamic Name Range

State = OFFSET(Statement!$B$16,,,COUNTA(Statement!$B$16:$B$1002),6)

Formula

Cell B9 = B3

Cell B10 = IF(ISNA(VLOOKUP($B$9,Customer,3,0)),"",VLOOKUP($B$9,Customer,3,0))

Cell B11 = IF(ISNA(VLOOKUP($B$9,Customer,4,0)),"",VLOOKUP($B$9,Customer,4,0))

Cell B12 = IF(ISNA(VLOOKUP($B$9,Customer,5,0)),"",VLOOKUP($B$9,Customer,5,0))

Cell B13 = IF(ISNA(VLOOKUP($B$9,Customer,6,0)),"",VLOOKUP($B$9,Customer,6,0))

Cell B14 = IF(ISNA(VLOOKUP($B$9,Customer,7,0)),"",VLOOKUP($B$9,Customer,7,0))

Cell C3 = IF(B3="","",VLOOKUP(B3,Customer_ID,2,0))

Cell D15 =IF(D3="","",D3)

Cell F15 =IF(E3="","",E3)

Gambar Show Formula WorkSheets Statement

Data validation

Cell B3 = Customer_Name

Gambar WorkSheets Customer

Module

Sub Filter_Statement()

Dim DateBegin

Dim DateEnd

Dim code

Dim Rng As Range

On Error GoTo errHandler:

Set code = Worksheets("Statement").Range("C23")

Set Rng = Worksheets("Sales").Range("D4")

Application.ScreenUpdating = False

With Worksheets("Sales")

       If Worksheets("Sales").AutoFilterMode Then

      

         Worksheets("Sales").Range("D5").AutoFilter

        End If

    End With

DateBegin = Format(Worksheets("Statement").Range("D3").Value, "mm/dd/yy")

DateEnd = Format(Worksheets("Statement").Range("E3").Value, "mm/dd/yy")

If Worksheets("Statement").Range("D3").Value > Worksheets("Statement").Range("E3").Value Then

MsgBox " Your start value is wrong"

Exit Sub

Else

If DateBegin = "" Then

MsgBox "Masukkan Terlebih Dahulu Tanggal Mulai transaksi "

Exit Sub

End If

If DateEnd = "" Then

MsgBox "Masukkan Terlebih Dahulu Tanggal Akhir transaksi "

Exit Sub

End If

If code = "" Then code = "*"

Rng.AutoFilter Field:=1, Criteria1:=">=" & DateBegin, _

Operator:=xlAnd, Criteria2:="<=" & DateEnd

Rng.AutoFilter Field:=2, Criteria1:=code & "*"

Worksheets("Sales").Range("DataSale").SpecialCells(xlCellTypeVisible).Copy _

Destination:=Worksheets("Statement").Range("B17")

With Worksheets("Sales")

       If Worksheets("Sales").AutoFilterMode Then

      

         Worksheets("Sales").Range("D5").AutoFilter

        End If

    End With

End If

On Error GoTo 0

Exit Sub

errHandler:

MsgBox "There is no data"

End Sub

Sub Group()

Worksheets("Statement").Select

Worksheets("Statement").Range("State").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 5), _

Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub

Sub UnGroup()

Worksheets("Statement").Range("State").RemoveSubtotal

End Sub

Sub SavePDFStatement()

Application.ScreenUpdating = False

Opendialog = Application.GetSaveAsFilename("", filefilter:="PDF Files (*.pdf), *.pdf", _

Title:="Your Invoice")

If Opendialog = False Then

MsgBox "The operation was not successful"

Exit Sub

End If

With Worksheets("Statement")

.Range("B8:F" & Cells(Rows.Count, "B").End(xlUp).Row).Name = "StateRng"

End With

Set myrange = Worksheets("Statement").Range("StateRng")

Worksheets("Statement").PageSetup.PrintArea = "StateRng"

On Error Resume Next

myrange.ExportAsFixedFormat _

Type:=xlTypePDF, _

Filename:=Opendialog, _

Quality:=xlQualityStandard, _

IncludeDocProperties:=True, _

IgnorePrintAreas:=False, _

OpenAfterPublish:=True

On Error GoTo 0

ActiveSheet.DisplayPageBreaks = False

Application.ScreenUpdating = False

End Sub

Sub ClearStatement()

With Worksheets("Statement").Range("B17:G1000").Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent6

.TintAndShade = -0.499984740745262

End With

With Worksheets("Statement").Range("B17:G1000")

.Borders.LineStyle = xlNone

.ClearContents

End With

End Sub