Sub SetStyle()
Sheets("集計").Rows("2:1000").Delete
Sheets("必要数").Range("F2:F18").Value = ""
Sheets("必要数").Range("H2:H18").Value = ""
Sheets("必要数").Range("J2:J18").Value = ""
Sheets("必要数").Range("L2:L18").Value = ""
End Sub
Private Sub CommandButton1_Click()
yn = MsgBox("集計シートはクリアされます。よろしいですか?", vbYesNo)
If yn = vbNo Then Exit Sub
Call SetStyle
Set w = Sheets("リスト")
r = 2
Do While w.Cells(r, 2).Value <> ""
modelName = w.Cells(r, 2).Value
qty = w.Cells(r, 4).Value
Call RowsCopy(modelName, qty)
r = r + 1
Loop
Call MakeSummary
Sheets("集計").Cells(1, 1).CurrentRegion.Borders.LineStyle = True
Sheets("必要数").Activate
End Sub
Sub RowsCopy(modelName, qty)
Set w = Sheets("部品")
destRow = Sheets("集計").UsedRange.Rows.Count + 1
r = 2
Do While w.Cells(r, 1).Value <> ""
If w.Cells(r, 1).Value = modelName Then
w.Range("A" & r & ":G" & r & "").Copy Sheets("集計").Range("A" & destRow & ":G" & destRow & "")
Sheets("集計").Range("G" & destRow & "").Value = Sheets("集計").Range("G" & destRow & "") * qty
Sheets("集計").Range("H" & destRow & "").Value = "=E" & destRow & "*F" & destRow & "*G" & destRow & ""
destRow = destRow + 1
End If
r = r + 1
Loop
End Sub
Sub MakeSummary()
Set w = Sheets("必要数")
lastRow = Sheets("集計").UsedRange.Rows.Count
For r = 2 To 18
w.Cells(r, 3).Value = "=SUMIFS(集計!H2:H" & lastRow & ",集計!C2:C" & lastRow & ",A" & r & ",集計!D2:D" & lastRow & ",B" & r & ")"
w.Cells(r, 4).Value = "=SUMIFS(集計!G2:G" & lastRow & ",集計!C2:C" & lastRow & ",A" & r & ",集計!D2:D" & lastRow & ",B" & r & ")"
Next r
End Sub