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 #5. Membuat Template Invoice dan Laporan Penjualan

 

 

Dynamic Range

Action = Product!$J$9:$J$1000

ChartCategory =Template!$B$29:INDEX(Template!$B$29:$B$1000,COUNTA(Template!$B$29:$B$1000))

ChartSoldout =Template!$F$29:INDEX(Template!$F$29:$F$1000,COUNT(Template!$F$29:$F$1000))

ChartDate = Template!$K$29:INDEX(Template!$K$29:$J$1000,COUNT(Template!$K$29:$J$1000))

ChartAmount = 

Template!$O$29:INDEX(Template!$O$29:$O$1000,COUNTA(Template!$O$29:$O$1000))

Formula

Cell D24 = COUNTIF(Action,D23)

Cell F24 = COUNTIF(Action,F23)

Cell G24 = COUNTIF(Action,G23)

Cell N24 =  IF(L24="","",VLOOKUP(L24,Customer_ID,2,0))

 

Gambar Show FormulaWorkSheets Template

Data Validation

Cell B24 = Category

Cell C24 = =OFFSET(Product!$E$9,MATCH($B24,Category_Product,0)-1,0,COUNTIF(Category_Product,$B24),1)

Cell L24 = Customer_Name

Module

Sub Advanced_Products()

Dim Category

Dim Description

Dim Rng As Range

On Error GoTo Excell_Invoice:

Set Category = Worksheets("Template").Range("B24")

Set Description = Worksheets("Template").Range("C24")

Set Rng = Worksheets("Product").Range("F8")

Application.ScreenUpdating = False

With Worksheets("Product")

       If Worksheets("Product").AutoFilterMode Then

      

         Worksheets("Product").Range("F9").AutoFilter

        End If

    End With

If Category = "" Then Category = "*"

If Description = "" Then Description = "*"

Rng.AutoFilter Field:=3, Criteria1:=Category & "*"

Rng.AutoFilter Field:=4, Criteria1:=Description & "*"

Clear_Template1

Worksheets("Product").Range("D9:J1000").SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Template").Range("B29")

With Worksheets("Sales")

       If Worksheets("Product").AutoFilterMode Then

      

         Worksheets("Product").Range("F9").AutoFilter

        End If

    End With

On Error GoTo 0

Exit Sub

Excell_Invoice:

MsgBox "There is no data"

End Sub

Sub Clear_Template1()

With Worksheets("Template").Range("B29:H1000").Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent6

.TintAndShade = -0.499984740745262

End With

With Worksheets("Template").Range("B29:H1000")

.Borders.LineStyle = xlNone

.ClearContents

End With

End Sub

Sub Sales_Chart()

Dim DateBegin

Dim DateEnd

Dim code

Dim Rng As Range

On Error GoTo errHandler:

Set code = Worksheets("Template").Range("N24")

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("Template").Range("J24").Value, "mm/dd/yy")

DateEnd = Format(Worksheets("Template").Range("K24").Value, "mm/dd/yy")

If Worksheets("Template").Range("J24").Value > Worksheets("Template").Range("K24").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 & "*"

Call Clear_Template2

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

Destination:=Worksheets("Template").Range("J29")

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 Clear_Template2()

With Worksheets("Template").Range("J29:O1000").Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent6

.TintAndShade = -0.499984740745262

End With

With Worksheets("Template").Range("J29:O1000")

.Borders.LineStyle = xlNone

.ClearContents

End With

End Sub

Sub Reorder_Now()

Dim Remarks

Dim Rng As Range

On Error GoTo Excell_Invoice:

Set Remarks = Worksheets("Template").Range("Z36")

Set Rng = Worksheets("Product").Range("J8")

Application.ScreenUpdating = False

With Worksheets("Product")

       If Worksheets("Product").AutoFilterMode Then

      

         Worksheets("Product").Range("J9").AutoFilter

        End If

    End With

If Remarks = "" Then Remarks = "*"

Rng.AutoFilter Field:=9, Criteria1:=Remarks & "*"

Clear_Template1

Worksheets("Product").Range("D9:J1000").SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Template").Range("B29")

With Worksheets("Sales")

       If Worksheets("Product").AutoFilterMode Then

      

         Worksheets("Product").Range("J9").AutoFilter

        End If

    End With

On Error GoTo 0

Exit Sub

Excell_Invoice:

MsgBox "There is no data"

End Sub