excel - 当工作表名称符合 VBA 标准时跨多个工作表的 SUMIFS

Question

嗨，有人知道如何编写这个宏吗？这是对的吗？还是有更简单的编码方式？喜欢 SUMIFS？

如果有 30 个产品，则有 30 个工作表，并且此产品代码将进一步分为 3 个不同的类别。所以我必须为每个类别创建额外的 3 个选项卡，并且需要总结这 30 个工作表中各个类别的值。

• 以 C 开头并以 6 结尾的产品称为 Lion
• 以C开头并以3结尾的产品称为Tiger
• 以 U 开头并以 4 结尾的产品称为 Bird

Sub Macro45()
'
' Macro45 Macro"CrossMult_Validation")

'
Dim ws As Worksheet
Dim lLastRow As Long, i As Long
Dim RunningSum As Range

For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 1) = "C" And Right(ws.Name, 1) = "6" Then
        With ws
        lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Parent.Sheets.Add After:=.Parent.Sheets(.Parent.Sheets.Count)
       
        For i = 1 To lastRowData
            .Cells(14, i + 1) = Application.WorksheetFunction.Sum(RunningSum)
        Next i
        .Name = "Lion"
        End With

For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 1) = "C" And Right(ws.Name, 1) = "3" Then
        With ws
        lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Parent.Sheets.Add After:=.Parent.Sheets(.Parent.Sheets.Count)

        For i = 1 To lastRowData
            .Cells(14, i + 1) = Application.WorksheetFunction.Sum(RunningSum)
        Next i
        .Name = "Tiger"
        End With


For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 1) = "U" And Right(ws.Name, 1) = "4" Then
        With ws
        lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Parent.Sheets.Add After:=.Parent.Sheets(.Parent.Sheets.Count)

        For i = 1 To lastRowData
            .Cells(14, i + 1) = Application.WorksheetFunction.Sum(RunningSum)
        Next i
        .Name = "Bird"
        End With
     End If
    Next ws

End Sub

标签： excelvba