Quick and Easy Inventory

How to create and manage inventory quickly and easily. In the last few videos we learnt how to create a database quickly and easily using a single worksheet. Can we apply a similar strategy for inventory creation and management using multiple worksheets? Watch the video:

Watch this video on YouTube.

Here’s the complete VBA code:

  1. Complete VBA code for the Add button:Sub addData()
    Dim i As Long, lastrow As Long, erow As Long
    lastrowP = Sheets(“Sheet1”).Range(“A” & rows.count).End(xlUp).row
    lastrowS = Sheets(“Sheet1”).Range(“K” & rows.count).End(xlUp).row’lastrow = Sheet1.Cells.Find(What:=”*”, After:=Range(“A1”), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).row
    erowP = lastrowP + 1
    erowS = lastrowS + 1If Sheet2.Range(“B8”) = “Purchase” Then
    Sheet1.Cells(erowP, 1) = Sheet2.Range(“B1”)
    Sheet1.Cells(erowP, 2) = Sheet2.Range(“B2”)
    Sheet1.Cells(erowP, 3) = Sheet2.Range(“B3”)
    Sheet1.Cells(erowP, 4) = Sheet2.Range(“B4”)
    Sheet1.Cells(erowP, 5) = Sheet2.Range(“B5”)
    Sheet1.Cells(erowP, 6) = Sheet2.Range(“B6”)
    Sheet1.Cells(erowP, 7) = Sheet2.Range(“B7”)Else

    Sheet1.Cells(erowS, 11) = Sheet2.Range(“B1”)
    Sheet1.Cells(erowS, 12) = Sheet2.Range(“B2”)
    Sheet1.Cells(erowS, 13) = Sheet2.Range(“B3”)
    Sheet1.Cells(erowS, 14) = Sheet2.Range(“B4”)
    Sheet1.Cells(erowS, 15) = Sheet2.Range(“B5”)
    Sheet1.Cells(erowS, 16) = Sheet2.Range(“B6”)
    Sheet1.Cells(erowS, 17) = Sheet2.Range(“B7”)
    End If

    End Sub

  2.  VBA code for the Clear button:Sub clearData()
    Dim reply As String
    reply = InputBox(“Are you sure you wish to clear the data? Enter y, if yes.”, “Clear Data”, , xpos:=4320, ypos:=5760)
    If reply = “y” Then
    Range(“B1:B8”).SpecialCells(xlCellTypeConstants, 23).ClearContents
    Else
    Exit Sub
    End IfEnd Sub
  3. Code for the Inventory report button:Sub createInventoryReport()
    Dim i As Long, lastrow As Long, qtyPurchased As Long, row As Long, qtySold As Long, qtyInStock As Long
    Dim inventoryid As String
    lastrow = Sheets(“Sheet1”).Range(“A” & rows.count).End(xlUp).row
    Dim erow As Long
    erow = Sheets(“Sheet3”).Range(“A” & rows.count).End(xlUp).Offset(1, 0).row
    qtyPurchased = 0
    qtySold = 0
    qtyInStock = 0inventoryid = InputBox(“Please enter the inventory ID.”)
    For i = 3 To lastrow
    If Cells(i, 1) = inventoryid Then
    qtyPurchased = qtyPurchased + Cells(i, 5)
    qtySold = qtySold + Cells(i, 15)
    row = i
    End IfNext iSheet1.Range(Cells(row, 1), Cells(row, 3)).Copy Destination:=Sheet3.Cells(erow, 1)
    Application.CutCopyMode = False
    Sheet3.Cells(erow, 4) = qtyPurchased
    Sheet3.Cells(erow, 5) = qtySold
    qtyInStock = qtyPurchased – qtySold
    Sheet3.Cells(erow, 6) = qtyInStock

    End Sub

  4. VBA code for the Monthly Report button:Sub createMonthlyReport()
    Dim i As Long, lastrow As Long, qtyPurchased As Long, row As Long, qtySold As Long, qtyInStock As Long
    Dim inventoryid As String
    lastrow = Sheets(“Sheet1”).Range(“A” & rows.count).End(xlUp).row
    Dim erow As Long
    erow = Sheets(“Sheet4”).Range(“A” & rows.count).End(xlUp).Offset(1, 0).row
    qtyPurchased = 0
    qtySold = 0
    qtyInStock = 0
    Dim startDate As Date, endDate As Date
    inventoryid = InputBox(“Please enter the inventory ID.”)
    startDate = InputBox(“Enter start of the report month.”)
    endDate = InputBox(“Enter the end of the report month”)Sheet1.Activate
    For i = 3 To lastrow
    If Cells(i, 1) = inventoryid And Cells(i, 7) >= startDate And Cells(i, 7) <= endDate Then
    qtyPurchased = qtyPurchased + Cells(i, 5)
    qtySold = qtySold + Cells(i, 15)
    row = i
    End IfNext iSet rngSource = Sheet1.Range(Cells(row, 1), Cells(row, 3))

    rngSource.Copy

    Sheet4.Activate

    Set rngDestination = Sheet4.Range(Cells(erow, 1), Cells(erow, 3))
    rngDestination.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheet4.Cells(erow, 4) = MonthName(Month(startDate))
    Sheet4.Cells(erow, 5) = qtyPurchased
    Sheet4.Cells(erow, 6) = qtySold
    qtyInStock = qtyPurchased – qtySold
    Sheet4.Cells(erow, 7) = qtyInStock

    End Sub

    Download a sample file for practice: