VBA 宏：分类统计多个工作表中材料使用量并自动更新结果表格

"""\nSub \u8ba1\u7b97\u6750\u6599\u4f7f\u7528\u91cf()\n Dim ws As Worksheet\n Dim wsResult As Worksheet\n Dim lastRow As Long\n Dim materialCol As Range\n Dim quantityCol As Range\n Dim resultRow As Long\n Dim material As String\n Dim quantity As Double\n \n ' \u8bbe\u7f6e\u7ed3\u679c\u8868\u683c\n On Error Resume Next\n Set wsResult = ThisWorkbook.Worksheets("\u7ed3\u679c\")\n On Error GoTo 0\n \n If wsResult Is Nothing Then\n ' \u5982\u679c\u7ed3\u679c\u8868\u683c\u4e0d\u5b58\u5728\uff0c\u5219\u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u5de5\u4f5c\u8868\n Set wsResult = ThisWorkbook.Worksheets.Add\n wsResult.Name = "\u7ed3\u679c"\n Else\n ' \u5982\u679c\u7ed3\u679c\u8868\u683c\u5b58\u5728\uff0c\u5219\u6e05\u7a7a\u8868\u683c\u5185\u5bb9\n wsResult.Cells.Clear\n End If\n \n ' \u5728\u7ed3\u679c\u8868\u683c\u4e2d\u6dfb\u52a0\u8868\u5934\n wsResult.Range("A1").Value = "\u6750\u6599"\n wsResult.Range("B1").Value = "\u603b\u4f7f\u7528\u91cf"\n \n ' \u5faa\u73af\u8d4f\u9009\u6bcf\u4e2a\u5de5\u4f5c\u8868\n For Each ws In ThisWorkbook.Worksheets\n ' \u8df3\u8fc7\u7ed3\u679c\u8868\u683c\n If ws.Name <> "\u7ed3\u679c" Then\n ' \u627e\u5230\u6750\u6599\u548c\u6570\u91cf\u5217\n Set materialCol = ws.Rows(1).Find("\u6750\u6599\")\n Set quantityCol = ws.Rows(1).Find("\u6570\u91cf\")\n \n ' \u5982\u679c\u627e\u5230\u6750\u6599\u548c\u6570\u91cf\u5217\n If Not materialCol Is Nothing And Not quantityCol Is Nothing Then\n ' \u83b7\u5f97\u6750\u6599\u548c\u6570\u91cf\u5217\u7684\u6700\u540e\u4e00\u884c\n lastRow = ws.Cells(ws.Rows.Count, materialCol.Column).End(xlUp).Row\n \n ' \u5faa\u73af\u8d4f\u9009\u6bcf\u884c\u6570\u636e\n For i = 2 To lastRow\n ' \u83b7\u5f97\u6750\u6599\u548c\u6570\u91cf\n material = ws.Cells(i, materialCol.Column).Value\n quantity = ws.Cells(i, quantityCol.Column).Value\n \n ' \u5728\u7ed3\u679c\u8868\u683c\u4e2d\u627e\u5230\u6750\u6599\n resultRow = wsResult.Cells(wsResult.Rows.Count, 1).End(xlUp).Row\n Set materialCol = wsResult.Range("A2:A" & resultRow).Find(material)\n \n ' \u5982\u679c\u5728\u7ed3\u679c\u8868\u683c\u4e2d\u627e\u5230\u6750\u6599\uff0c\u5219\u66f4\u65b0\u5bf9\u5e94\u884c\u7684\u603b\u4f7f\u7528\u91cf\n If Not materialCol Is Nothing Then\n wsResult.Cells(materialCol.Row, 2).Value = wsResult.Cells(materialCol.Row, 2).Value + quantity\n Else\n ' \u5982\u679c\u5728\u7ed3\u679c\u8868\u683c\u4e2d\u672a\u627e\u5230\u6750\u6599\uff0c\u5219\u5728\u4e0b\u4e00\u884c\u6dfb\u52a0\u65b0\u7684\u6750\u6599\u548c\u603b\u4f7f\u7528\u91cf\n resultRow = resultRow + 1\n wsResult.Cells(resultRow, 1).Value = material\n wsResult.Cells(resultRow, 2).Value = quantity\n End If\n Next i\n End If\n End If\n Next ws\n \n ' \u81ea\u52a8\u8c03\u6574\u7ed3\u679c\u8868\u683c\u7684\u5217\u5e45\n wsResult.Columns.AutoFit\nEnd Sub\n"""\n\u4f7f\u7528\u8be5\u5fae\u7684\u65f6\uff0c\u53ea\u9700\u6309\u4e0b`Alt + F11\u5f00\u59cb\u5fae\u7684\u7f16\u8f91\u5668\uff0c\u5c06\u4e0a\u8ff0\u4ee3\u7801\u590d\u5236\u7c98\u8d34\u5230\ThisWorkbook\u7684\u4ee3\u7801\u7a97\u53e3\u4e2d\u3002\u7136\u540e\uff0c\u6309\u4e0b\F5`\u8fd0\u884c\u5fae\u7684\u3002\n\n\u8fd0\u884c\u5b8c\u6bd5\u540e\uff0c\u5c31\u4f1a\u5728\u5de5\u4f5c\u5e93\u4e2d\u521b\u5efa\u4e00\u4e2a\u540d\u5b57\u4e3a"\u7ed3\u679c"\u7684\u5de5\u4f5c\u8868\uff0c\u5e76\u5c06\u5206\u7c7b\u7edf\u8ba1\u7684\u7ed3\u679c\u8f93\u51fa\u5230\u8be5\u5de5\u4f5c\u8868\u4e2d\u3002\u5982\u679c\u9700\u8981\u66f4\u65b0\u7ed3\u679c\u8868\u683c\uff0c\u53ea\u9700\u518d\u6b21\u8fd0\u884c\u5fae\u7684\u5c31\u53ef\u4ee5\u3002\n\n\u6ce8\u610f\uff1a\u8bf7\u786e\u4fdd\u6bcf\u4e2a\u5de5\u4f5c\u8868\u4e2d\u5305\u542b"\u6750\u6599"\u548c"\u6570\u91cf"\u4e24\u5217\uff0c\u5e76\u4e14\u8868\u5934\u4f4d\u4e8e\u7b2c\u4e00\u884c\u3002