多工作簿同名工作表数据汇总到汇总表的同名工作表上

l_hl2008 · 发表于 2024-1-3 00:50

汇总求助，要求如示例，谢谢！

liulang0808 · 发表于 2024-1-3 08:25

Sub 按钮205_Click()
Set fso = CreateObject("scripting.filesystemobject")
Application.ScreenUpdating = False
For Each f In fso.getfolder(ThisWorkbook.Path).Files
      If InStr(f.Name, ThisWorkbook.Name) = 0 Then
         With Workbooks.Open(f)
            For j = 6 To 30
                  If InStr(.Sheets("统计表").Cells(7, j), "计") = 0 Then
                  ThisWorkbook.Sheets("统计表").Cells(8, j) = Val(ThisWorkbook.Sheets("统计表").Cells(8, j)) + Val(.Sheets("统计表").Cells(8, j))
                  End If
            Next j
            For j = 3 To 27
                  If InStr(.Sheets("统计表").Cells(13, j), "计") = 0 Then
                  ThisWorkbook.Sheets("统计表").Cells(14, j) = Val(ThisWorkbook.Sheets("统计表").Cells(14, j)) + Val(.Sheets("统计表").Cells(14, j))
                  End If
            Next j
            For j = 3 To 28
                  If InStr(.Sheets("统计表").Cells(19, j), "计") = 0 Then
                  ThisWorkbook.Sheets("统计表").Cells(20, j) = Val(ThisWorkbook.Sheets("统计表").Cells(20, j)) + Val(.Sheets("统计表").Cells(20, j))
                  End If
            Next j

            For j = 5 To 16
                  If InStr(.Sheets("呈报表").Cells(5, j), "计") = 0 Then
                     For i = 6 To 13
                        ThisWorkbook.Sheets("呈报表").Cells(i, j) = Val(ThisWorkbook.Sheets("呈报表").Cells(i, j)) + Val(.Sheets("呈报表").Cells(i, j))
                     Next i
                  End If
            Next j
            .Close False
         End With
      End If
Next f
Application.ScreenUpdating = True
End Sub

liulang0808 · 发表于 2024-1-3 08:26

示例 (4).zip (121.45 KB, 下载次数: 29)
猜测了一下，供参考。。。

l_hl2008 · 发表于 2024-1-3 15:30

liulang0808 发表于 2024-1-3 08:26
猜测了一下，供参考。。。

谢谢！学习先

l_hl2008 · 发表于 2024-1-3 21:46

liulang0808 发表于 2024-1-3 08:26
猜测了一下，供参考。。。

老师晚上好，多次按按钮如何限制重复汇总，谢谢

liulang0808 · 发表于 2024-1-3 23:22

l_hl2008 发表于 2024-1-3 21:46
老师晚上好，多次按按钮如何限制重复汇总，谢谢

设置一个参数，增加判断条件，或者进行内容清空

l_hl2008 · 发表于 2024-1-3 23:53

liulang0808 发表于 2024-1-3 23:22
设置一个参数，增加判断条件，或者进行内容清空

试增加几个语句，不知合不合理，请老师指正

  Set fso = CreateObject("scripting.FileSystemObject")
Application.ScreenUpdating = False

For j = 6 To 30
      ThisWorkbook.Sheets("统计表").Cells(8, j) = 0
Next j
For j = 3 To 27
      ThisWorkbook.Sheets("统计表").Cells(14, j) = 0
Next j
For j = 3 To 28
      ThisWorkbook.Sheets("统计表").Cells(20, j) = 0
Next j
For j = 5 To 16
      For i = 6 To 13
         ThisWorkbook.Sheets("呈报表").Cells(i, j) = 0
      Next i
Next j

For Each f In fso.getfolder(ThisWorkbook.Path).Files
      If InStr(f.Name, ThisWorkbook.Name) = 0 Then
         With Workbooks.Open(f)
            For j = 6 To 30
                  If InStr(.Sheets("统计表").Cells(7, j), "计") = 0 Then
                  ThisWorkbook.Sheets("统计表").Cells(8, j) = Val(ThisWorkbook.Sheets("统计表").Cells(8, j)) + Val(.Sheets("统计表").Cells(8, j))
                  End If
            Next j
            For j = 3 To 27
                  If InStr(.Sheets("统计表").Cells(13, j), "计") = 0 Then
                  ThisWorkbook.Sheets("统计表").Cells(14, j) = Val(ThisWorkbook.Sheets("统计表").Cells(14, j)) + Val(.Sheets("统计表").Cells(14, j))
                  End If
            Next j
            For j = 3 To 28
                  If InStr(.Sheets("统计表").Cells(19, j), "计") = 0 Then
                  ThisWorkbook.Sheets("统计表").Cells(20, j) = Val(ThisWorkbook.Sheets("统计表").Cells(20, j)) + Val(.Sheets("统计表").Cells(20, j))
                  End If
            Next j

            For j = 5 To 16
                  If InStr(.Sheets("呈报表").Cells(5, j), "计") = 0 Then
                     For i = 6 To 13
                        ThisWorkbook.Sheets("呈报表").Cells(i, j) = Val(ThisWorkbook.Sheets("呈报表").Cells(i, j)) + Val(.Sheets("呈报表").Cells(i, j))
                     Next i
                  End If
            Next j
            .Close False
         End With
      End If
Next f
Application.ScreenUpdating = True
End Sub

		自动登录	找回密码
密码			免费注册

[求助] 多工作簿同名工作表数据汇总到汇总表的同名工作表上