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 # 2 Membuat Worksheets Add Stock

Formula

Cell G4 = VLOOKUP(B4&C4,CHOOSE({1,2},Product!$D$9:$D$1000&Product!$E$9:$E$1000,Product!$F$9:$F$1000),2,FALSE)

Press Control + Shift and Enter

Gambar Show Formula WorkSheets AddStock

Dynamic Name Range

• AddStock = AddStock!$D$4:$G$4
• Stock_Clear = AddStock!$B$4:$F$4 

Data Validation

• Cell B4 = Category
• Cell C4  =OFFSET(Product!$E$9,MATCH($B4,Category_Product,0)-1,0,COUNTIF(Category_Product,$B4),1)

Module

  Sub Add_Stock()

On Error GoTo Excell_Invoice:

Dim RsAddStock As Range 'destination range

Dim wsAddStock As Worksheet

'destination variable

'Set RsAddStock = Worksheets("AddStock").range("I8")

Set RsAddStock = Worksheets("AddStock").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

Set wsAddStock = Worksheets("AddStock")

Application.ScreenUpdating = False

If wsAddStock.Range("D4") = "" Then

MsgBox "Date is Blank"

Exit Sub

ElseIf wsAddStock.Range("E4") = "" Then

MsgBox "Supplier Name is Blank "

Exit Sub

ElseIf wsAddStock.Range("F4") = "" Then

MsgBox "Quantity is Blank "

Exit Sub

ElseIf wsAddStock.Range("G4") = "" Then

MsgBox "Code is Blank "

Exit Sub

Else

'give the user a chance to exit here

Select Case MsgBox _

("You are about to add stock." _

& vbCrLf & "Check everything before you proceed", _

vbYesNo Or vbExclamation, "Are you sure?")

Case vbYes

Case vbNo

Exit Sub

End Select

'copy and paste data without selecting,first sheet,sourse variable

Set AddStockSrc = Worksheets("AddStock").Range("AddStock")

AddStockSrc.Copy

RsAddStock.PasteSpecial xlPasteValues

'empty clipboard

Application.CutCopyMode = False

'confirmation message

MsgBox "Your stock hase been has been added." _

& vbCrLf & "and the totals have been sent to Accounts"

'clear the invoicce

wsAddStock.Range("Stock_Clear").ClearContents

wsAddStock.Select

Application.ScreenUpdating = True

Update

End If

Exit Sub

Excell_Invoice:

MsgBox " We have a problem"

End Sub

Sub Update()

On Error GoTo Excell_Invoice

Application.ScreenUpdating = False

Dim wsAddStock As Worksheet

Set wsAddStock = Worksheets("AddStock")

wsAddStock.Activate

With wsAddStock

.Range("E10:E" & Cells(Rows.Count, "E").End(xlUp).Row).Name = "InCode"

.Range("D10:D" & Cells(Rows.Count, "D").End(xlUp).Row).Name = "InQty"

End With

On Error GoTo 0

Exit Sub

Excell_Invoice:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Update of Module CopyTo"

End Sub