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: