在庫のExcelを開いて Alt+F11 を押すと、VBAの画面が開きます。上のメニューの「挿入」→「標準モジュール」を選び、出てきた白い画面に Ctrl+V で貼り付けて、VBAの画面を閉じます。
下は、撮影した回にCopilotが出したコードの全文です(見本のExcel用)。
Option Explicit
Sub 在庫更新()
Dim wb As Workbook
Dim wsStock As Worksheet
Dim wsLog As Worksheet
Dim wsCheck As Worksheet
Dim dicIn As Object
Dim dicOut As Object
Dim dicStock As Object
Dim lastStockRow As Long
Dim lastLogRow As Long
Dim i As Long
Dim checkRow As Long
Dim itemCode As String
Dim category As String
Dim quantity As Double
Dim currentStock As Double
Dim orderPoint As Double
Dim key As Variant
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
Set wsStock = wb.Worksheets("在庫一覧")
Set wsLog = wb.Worksheets("入出庫記録")
Set dicIn = CreateObject("Scripting.Dictionary")
Set dicOut = CreateObject("Scripting.Dictionary")
Set dicStock = CreateObject("Scripting.Dictionary")
dicIn.CompareMode = vbTextCompare
dicOut.CompareMode = vbTextCompare
dicStock.CompareMode = vbTextCompare
lastStockRow = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row
lastLogRow = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row
'前回の計算結果、状態、色を消去
If lastStockRow >= 2 Then
wsStock.Range("F2:G" & lastStockRow).ClearContents
wsStock.Range("A2:G" & lastStockRow).Interior.Pattern = xlNone
End If
'前回の「要確認」シートを削除
Application.DisplayAlerts = False
On Error Resume Next
wb.Worksheets("要確認").Delete
On Error GoTo ErrorHandler
Application.DisplayAlerts = True
'在庫一覧の品番を辞書へ登録
For i = 2 To lastStockRow
itemCode = Trim$(CStr(wsStock.Cells(i, "A").Value))
If itemCode <> "" Then
If Not dicStock.Exists(itemCode) Then
dicStock.Add itemCode, i
End If
End If
Next i
'入出庫記録を品番ごとに集計
For i = 2 To lastLogRow
itemCode = Trim$(CStr(wsLog.Cells(i, "B").Value))
category = Trim$(CStr(wsLog.Cells(i, "D").Value))
If IsNumeric(wsLog.Cells(i, "E").Value) Then
quantity = CDbl(wsLog.Cells(i, "E").Value)
Else
quantity = 0
End If
If itemCode <> "" Then
If category = "入庫" Then
If dicIn.Exists(itemCode) Then
dicIn(itemCode) = dicIn(itemCode) + quantity
Else
dicIn.Add itemCode, quantity
End If
ElseIf category = "出庫" Then
If dicOut.Exists(itemCode) Then
dicOut(itemCode) = dicOut(itemCode) + quantity
Else
dicOut.Add itemCode, quantity
End If
End If
End If
Next i
'現在の在庫と状態を更新
For Each key In dicStock.Keys
i = CLng(dicStock(key))
currentStock = 0
orderPoint = 0
If IsNumeric(wsStock.Cells(i, "D").Value) Then
currentStock = CDbl(wsStock.Cells(i, "D").Value)
End If
If dicIn.Exists(CStr(key)) Then
currentStock = currentStock + CDbl(dicIn(CStr(key)))
End If
If dicOut.Exists(CStr(key)) Then
currentStock = currentStock - CDbl(dicOut(CStr(key)))
End If
wsStock.Cells(i, "F").Value = currentStock
If IsNumeric(wsStock.Cells(i, "E").Value) Then
orderPoint = CDbl(wsStock.Cells(i, "E").Value)
If currentStock <= orderPoint Then
wsStock.Cells(i, "G").Value = "発注"
wsStock.Range("A" & i & ":G" & i).Interior.Color = RGB(255, 199, 206)
End If
End If
Next key
'新しい「要確認」シートを作成
Set wsCheck = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
wsCheck.Name = "要確認"
wsCheck.Range("A1:G1").Value = Array( _
"入出庫記録の行番号", "日付", "品番", "品名", "区分", "数量", "担当")
With wsCheck.Range("A1:G1")
.Font.Bold = True
.Font.Color = RGB(255, 255, 255)
.Interior.Color = RGB(31, 56, 100)
.HorizontalAlignment = xlCenter
End With
checkRow = 2
'在庫一覧にない品番を要確認へ書き出す
For i = 2 To lastLogRow
itemCode = Trim$(CStr(wsLog.Cells(i, "B").Value))
If itemCode <> "" Then
If Not dicStock.Exists(itemCode) Then
wsCheck.Cells(checkRow, "A").Value = i
wsCheck.Cells(checkRow, "B").Value = wsLog.Cells(i, "A").Value
wsCheck.Cells(checkRow, "C").Value = wsLog.Cells(i, "B").Value
wsCheck.Cells(checkRow, "D").Value = wsLog.Cells(i, "C").Value
wsCheck.Cells(checkRow, "E").Value = wsLog.Cells(i, "D").Value
wsCheck.Cells(checkRow, "F").Value = wsLog.Cells(i, "E").Value
wsCheck.Cells(checkRow, "G").Value = wsLog.Cells(i, "F").Value
checkRow = checkRow + 1
End If
End If
Next i
wsCheck.Columns("A:G").AutoFit
wsCheck.Columns("B").NumberFormatLocal = "m/d"
If checkRow = 2 Then
wsCheck.Cells(2, "A").Value = "在庫一覧にない品番はありません。"
wsCheck.Range("A2:G2").Merge
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "在庫の更新が完了しました。", vbInformation
Exit Sub
ErrorHandler:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "在庫を更新できませんでした。" & vbCrLf & _
"エラー内容:" & Err.Description, vbExclamation
End Sub